-- --------------------------------------------------------------------------
-- File:        referenced_by.sql
--
-- Autor:       Martin Zahn
--
-- Version:     24.08.2009
--
-- Purpose:     a.TABLE_NAME (Parent) is referenced by Tables (Childs)
--
-- ToDo:        Replace a.TABLE_NAME (Parent) and a.OWNER for your Needs
-- --------------------------------------------------------------------------
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
COLUMN  tab_owner   FORMAT A10 HEADING "Owner";
COLUMN  tab_name_pk FORMAT A45 HEADING "Table.Column (Parent) is";
COLUMN  tab_name_fk FORMAT A45 HEADING "referenced by Table.Column (Child)";
--
SELECT a.owner as tab_owner,
       a.table_name || '.' || d.column_name as tab_name_pk,
       b.table_name || '.' || c.column_name as tab_name_fk
FROM   DBA_CONSTRAINTS a,
       DBA_CONSTRAINTS b,
       DBA_CONS_COLUMNS c,
       DBA_CONS_COLUMNS d
WHERE  a.table_name = UPPER('YOUR-TABLE')
  AND  a.owner = UPPER('YOUR-SCHEMA')
  AND  b.r_constraint_name = a.constraint_name
  AND  b.r_owner = a.owner
  AND  b.constraint_type = 'R'
  AND  a.constraint_type IN ('P','U')
  AND  b.constraint_name = c.constraint_name
  AND  a.constraint_name = d.constraint_name
  AND  b.owner = c.owner
  AND  b.table_name = c.table_name
  AND  a.owner = d.owner
  AND  a.table_name = d.table_name
  AND  c.position = d.position
ORDER BY 1,2;

-- --------------------------------------------------------------------------
-- File:        references.sql
--
-- Autor:       Martin Zahn
--
-- Version:     24.08.2009
--
-- Purpose:     a.TABLE_NAME (Child) references which Tables (Parent)
--
-- ToDo:        Replace a.TABLE_NAME (Child) and a.OWNER for your Needs
-- --------------------------------------------------------------------------
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
COLUMN  tab_owner   FORMAT A10 HEADING "Owner";
COLUMN  tab_name_fk FORMAT A45 HEADING "Table.Column (Child) references";
COLUMN  tab_name_pk FORMAT A45 HEADING "Table.Column (Parent)";
--
SELECT  a.owner tab_owner,
        a.table_name || '.' || c.column_name as tab_name_fk,
        b.table_name || '.' || d.column_name as tab_name_pk
FROM    dba_constraints a,
        dba_constraints b,
        dba_cons_columns c,
        dba_cons_columns d
WHERE   a.table_name = UPPER('YOUR-TABLE')
  AND   a.owner = UPPER('YOUR-SCHEMA')
  AND   a.r_constraint_name = b.constraint_name
  AND   a.r_owner = b.owner
  AND   a.constraint_type = 'R'
  AND   b.constraint_type IN ('P','U')
  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   c.position = d.position
  ORDER BY 1,2;