--DESCRIPTION
--List all the constraints on a table.
--
--INPUTS
--  Param1   A table name, 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

--Dissect 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 primary key and unique constraints.
--
--Set up the page title.
TTITLE LEFT '   ' constraint_name_var ' ' constraint_type_var -
       ' ' status_var SKIP 1
       
--Run the query to display primary and unuque constarints.
SELECT ac.constraint_name,
       DECODE(ac.constraint_type,
              'P','PRIMARY KEY','U','UNIQUE') constraint_type_desc,
       ' ' table_name,
       '      ' ident,
       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
--
--Set up 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,
       '      ' ident,
       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
--
--Set up the page title for the check constraints 
TTITLE LEFT '   ' constraint_name_var ' CHECK ' status_var SKIP 1


SELECT ac.constraint_name,
       '      ' ident,
       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
