— 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