— mdw modified to not stick to current user
— and to allow owner to be limited.
— 23/4/04
— nb shit table aliases
— and reciprocal bit bust
— All in all a bit of a crap query
REM Displays all foreign keys for a given table, plus all foreign key references
REM from other tables to this one
REM Modified from the ‘forgnkey.sql’ script on page 439 of ‘Oracle
REM Performance Tuning’
REM SJM, 21.1.98

set echo off
set pages 32

accept tablename prompt ‘View constraints on table: ‘
accept tableowner prompt ‘for tables owned by: ‘
set verify off
column Constraint_from_to format A63
column Constraint_name format A16
spool tab_cols.lst
select unique a.constraint_name,a.table_name||’.’||c.column_name ||’ -> ‘||b.table_name||’.’||d.column_name Constraint_from_to
from dba_constraints  a,
     dba_constraints  b,
     dba_cons_columns c,
     dba_cons_columns d
where
a.r_constraint_name   = b.constraint_name
and a.constraint_type = ‘R’
and b.constraint_type = ‘P’
and a.r_owner         = b.owner
and a.constraint_name = c.constraint_name
and b.constraint_name = d.constraint_name
and a.owner           = c.owner
and a.table_name      = c.table_name
and b.owner           = d.owner
and b.table_name      = d.table_name
and b.constraint_name = d.constraint_name
and a.table_name      like upper(nvl(‘&&tablename’,’whoops’)||’%’)
and a.owner           like upper(‘&&tableowner’||’%’);

PROMPT Reciprocal constraints:

select unique a.constraint_name,a.table_name||’.’||c.column_name ||’ -> ‘||b.table_name||’.’||d.column_name Constraint_from_to
from dba_constraints a
   , dba_constraints b
   , dba_cons_columns c
   , dba_cons_columns d
where
a.r_constraint_name   = b.constraint_name
and a.constraint_type = ‘R’
and b.constraint_type = ‘P’
and a.r_owner         = b.owner
and a.constraint_name = c.constraint_name
and b.constraint_name = d.constraint_name
and a.owner           = c.owner
and a.table_name      = c.table_name
and b.owner           = d.owner
and b.table_name      = d.table_name
and b.table_name      like upper(nvl(‘&&tablename’,’whoops’)||’%’)
and b.owner           like upper(‘&&tableowner’||’%’);
spool off
clear colu