All Forums Database
akhil.bansal@gmail.com 19 posts Joined 06/05
07 Jun 2006
Finding Foreign Key for Primary Key Column

Hi,

Is there is a way to find foreign key columns that references to a primary key ?

i.e I have table PrimaryTable which has Col1 as primary key. I want to find out the tables having columns that have foreign key constraint defined on it which reffers to PromaryTable.col1.


Regards

DEEPU 9 posts Joined 10/05
07 Jun 2006

You can use the following system views.
DBC.RI_CHILD_TABLES
DBC.RI_DISTINCT_CHILDREN
DBC.RI_DISTINCT_PARENTS
DBC.RI_PARENT_TABLES

j355ga 92 posts Joined 12/05
07 Jun 2006

If you are using V2R6 then this will identify all dependencies. Replace the literals with the db and table you want to trace all dependencies from. I had to use a staging table because the view dbc.RI_Distinct_Children was causing missing data in the WITH RECURSIVE clause.

Create Volatile Table ri As (
Select
childdb,
childtable,
parentdb,
parenttable
From dbc.RI_Distinct_Children
) With data
On
Commit preserve rows;

With Recursive RI_LOOKUP (parentdb, parenttable, depth) As
(
Select root.parentdb, root.parenttable, 0 As depth
From ri root
Where root.childtable='CHILD_TABLE'
And root.childdb='CHILD_DB'
Union All
Select indirect.parentdb, indirect.parenttable, direct.depth + 1
From RI_LOOKUP direct, ri indirect
Where direct.parenttable = indirect.childtable
)
Select *
From RI_LOOKUP
Order By Depth Desc;

Drop Table ri;

You must sign in to leave a comment.