rem ********************************************************************
rem * Author            : Rick Kupcunas
rem * Original          : 17-MAY-95
rem * Description       : Shows constraints that make reference to a table
rem * Usage             : start show_con.sql <date>  e.g. 03-MAR-95
rem *                                        <Program Name>
rem *                                        <Max run time (min.)>
rem *
rem ********************************************************************

spool show_con2.rslt;

set pagesize 100;
set linesize 132;
set long 30;
break on "STATUS" on "TYPE" on "TABLE" on "CONSTRAINT";

select distinct 'ALL CONSTRAINTS REFERENCING: '||owner||'.'||table_name "PURPOSE" 
from sys.dba_constraints
  where table_name = upper('&&TABLE_NAME');

select A.status                       		"STATUS",
       A.constraint_type              		"TYPE",
       substr(A.table_name,1,25) 			"TABLE",
       substr(A.constraint_name,1,35) 		"CONSTRAINT",
       substr(A.r_constraint_name,1,25) 		"REFERENCED KEY:"
  from sys.all_constraints	A,
       sys.all_ind_columns 	B
   where A.owner NOT in ('SYS', 'SYSTEM')               and
         B.index_name           = A.R_CONSTRAINT_NAME   and
         B.index_owner          = A.R_OWNER             and
         B.table_name 		= upper('&&TABLE_NAME') and
         B.COLUMN_POSITION      = 1 
order by 1,2,3;

spool off;
undefine TABLE_NAME;

