All Forums Database
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_TABLESDBC.RI_DISTINCT_CHILDRENDBC.RI_DISTINCT_PARENTSDBC.RI_PARENT_TABLES

j355ga 100 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, parenttableFrom dbc.RI_Distinct_Children) With data OnCommit preserve rows;With Recursive RI_LOOKUP (parentdb, parenttable, depth) As(Select root.parentdb, root.parenttable, 0 As depthFrom ri rootWhere root.childtable='CHILD_TABLE' And root.childdb='CHILD_DB'Union AllSelect indirect.parentdb, indirect.parenttable, direct.depth + 1From RI_LOOKUP direct, ri indirectWhere direct.parenttable = indirect.childtable)Select *From RI_LOOKUPOrder By Depth Desc;Drop Table ri;

Jeff

You must sign in to leave a comment.