see what foreign keys are pointing to this table.sql
— chk_ref_by.sql
— see what fks are pointing to the PK and UNQs on a table (or set of tables)
— mdw 24/04/01
set pause on
set pages 24
col owner          form a8 word wrap
col table_name     form a15 word wrap
col cons           form a15 word wrap
col r_owner        form a8 word wrap
col r_cons         form a15 word wrap
col r_tab          form a10 word wrap
spool chk_ref_by.lst
select
dc.owner
,dc.table_name
,dc.constraint_name   cons
,dc.constraint_type   t
,dc2.owner            r_owner
,dc2.constraint_name  r_cons
,dc2.table_name       r_tab
from dba_constraints    dc
   ,dba_constraints    dc2
where dc2.r_owner          =  dc.owner
and  dc2.r_constraint_name =  dc.constraint_name
and  dc.constraint_type in (‘P’,’U’)
and dc.table_name like nvl(upper(‘&tab_name’),’WHOOPS’)||’%’
and dc.owner like nvl(upper(‘&tab_own’)||’%’,user)
order by dc.table_name,dc.owner
/
—
clear columns
spool off
Discussion ¬