Code:
--DESCRIPTION
--List all the constraints on a table.
--
--INPUTS
-- Param 1 A table name, optionally qualified
-- optionally qualified by an owner name.
-- For example: "SYS.ALL_TAB_COLUMNS".
--
SET ECHO OFF
SET NEWPAGE 1
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 9999
SET RECSEP OFF
SET CONCAT ON
CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS
TTITLE OFF
BTITLE OFF
--Disect the input argument, and get the owner name and
--table name into two seperate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_table_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT DECODE (INSTR ('&&1', '.'),
0, USER, /*Default to current user.*/
UPPER (SUBSTR ('&&1', 1, INSTR ('&&1', '.') - 1))
) owner_name,
DECODE (INSTR ('&&1', '.'),
0, UPPER ('&&1'), /*Only the table name was passed in.*/
UPPER (SUBSTR ('&&1', INSTR ('&&1', '.') + 1))
) table_name
FROM DUAL;
--Clear column definitions so that no future queries
--affect the substitution variables that were just set.
CLEAR COLUMNS
--Format the columns
COLUMN constraint_name NOPRINT NEW_VALUE constraint_name_var
COLUMN constraint_type_desc NOPRINT NEW_VALUE constraint_type_var
COLUMN r_owner NOPRINT NEW_VALUE r_owner_var
COLUMN r_table_name NOPRINT NEW_VALUE r_table_name_var
COLUMN status NOPRINT NEW_VALUE status_var
COLUMN delete_rule_desc NOPRINT NEW_VALUE delete_rule_var
COLUMN search_condition FORMAT A50 WORD_WRAPPED
COLUMN column_name FORMAT A30
COLUMN r_column_name FORMAT A30
--This script breaks on each constraint, and each constraint
--has its own page title. The page title shows information
--relating to the constraint as a whole.
BREAK ON constraint_name SKIP PAGE
--Tell the user he is going to see constraints.
PROMPT CONSTRAINTS ON TABLE &&s_owner_name..&&s_table_name
--First show the primary key and unique constraints.
--
--Setup the page title.
TTITLE LEFT ' ' constraint_name_var ' ' constraint_type_var -
' ' status_var SKIP 1
--Run the query to display primary key and unique constraints.
SELECT ac.constraint_name,
DECODE (ac.constraint_type,
'P', 'PRIMARY KEY',
'U', 'UNIQUE'
) constraint_type_desc,
' ' table_name, ' ' spacer, acc.column_name, ac.status
FROM all_constraints ac, all_cons_columns acc
WHERE ac.owner = '&&s_owner_name'
AND ac.table_name = '&&s_table_name'
AND ac.owner = acc.owner
AND ac.constraint_name = acc.constraint_name
AND ac.constraint_type IN ('P', 'U')
ORDER BY DECODE (ac.constraint_type, 'P', 1, 'U', 2),
ac.constraint_name,
acc.POSITION;
--Then show the foreign key constraints
--
--Setup the page title for the foreign key constraints
TTITLE LEFT ' ' constraint_name_var ' ' status_var SKIP 1 -
' FOREIGN KEY TO ' r_owner_var '.' r_table_name_var SKIP 1
--Run the query to show foreign key constraints
SELECT ac.constraint_name, ' ' indent, acc.column_name,
r_acc.owner r_owner, r_acc.table_name r_table_name,
r_acc.column_name r_column_name
FROM all_constraints ac, all_cons_columns acc, all_cons_columns r_acc
WHERE ac.owner = '&&s_owner_name'
AND ac.table_name = '&&s_table_name'
AND ac.constraint_type = 'R'
AND ac.owner = acc.owner
AND ac.constraint_name = acc.constraint_name
AND ac.r_owner = r_acc.owner
AND ac.r_constraint_name = r_acc.constraint_name
AND acc.POSITION = r_acc.POSITION
ORDER BY ac.constraint_name, acc.POSITION;
--Then show the check constraints
--
--Setup the page title for the check constraints
TTITLE LEFT ' ' constraint_name_var ' CHECK ' status_var SKIP 1
SELECT ac.constraint_name, ' ' spacer, search_condition
FROM all_constraints ac
WHERE ac.owner = '&&s_owner_name'
AND ac.table_name = '&&s_table_name'
AND ac.constraint_type = 'C';
--Undefine variables and restore settings to their defaults.
UNDEFINE s_owner_name
UNDEFINE s_table_name
SET NEWPAGE 1
SET VERIFY ON
SET FEEDBACK ON
SET HEADING ON
SET PAGESIZE 14
SET RECSEP WRAPPED
CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS
TTITLE OFF
HTH
Reply With Quote