-- -------------------------------------------------------------------------- -- 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;