-- #############################################################################################
--
-- %Purpose: Show Primary and Foreign Key Relationsships
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
spool show_fk_pk_relations.lst
set feed off
set pagesize 10000
ttitle off
--
ttitle left  'Primary and Foreign Key Relationsships' skip 2
set feed off
set pagesize 10000
--
column datum new_value datum noprint
column for_owner format A5 heading 'Table|Owner'
column pri_tsname format A10 heading 'Tablespace'
column for_table format A17 heading 'From|Foreign|Table'
column for_col format A16 heading 'From|Foreign|Column'
column pri_table format A17 heading 'To|Primary|Table'
column pri_col format A16 heading 'To|Primary|Column'
break on for_owner skip 1

select  a.owner for_owner,
        e.tablespace_name pri_tsname,
        a.table_name for_table,
        c.column_name for_col,
        b.table_name pri_table,
        d.column_name pri_col
from dba_constraints a,
     dba_constraints b,
     dba_cons_columns c,
     dba_cons_columns d,
     dba_tables e
where a.owner not in ('SYS','SYSTEM')
  and 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 a.owner = c.owner
  and a.table_name = c.table_name
  and b.constraint_name = d.constraint_name
  and b.owner = d.owner
  and b.table_name = d.table_name
  and b.table_name = e.table_name
order by a.owner,a.table_name;
