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 pagesize 66

accept tablename prompt ‘View constraints on table: ‘
set verify off
column Constraint_from_to format A49

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.owner = (select user from dual)
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 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 a.table_name = upper(‘&&tablename’);

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.owner = (select user from dual)
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 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 = upper(‘&&tablename’);