Google
Information Storage and Retrieval: How to find out the referenced table and column name if the foreign key constraint name is given

Pages

Monday, November 29, 2010

How to find out the referenced table and column name if the foreign key constraint name is given

The following query will give the desired result:

select
ac.table_name child_tab,
acc1.column_name child_col,
ac.constraint_name foreign_key,
acc.constraint_name primary_key,
acc.table_name parent_tab,
acc.column_name parent_col
from
all_constraints ac,
all_cons_columns acc,
all_cons_columns acc1
where
ac.owner=acc.owner
and acc.constraint_name=ac.r_constraint_name
and ac.constraint_name=acc1.constraint_name
and ac.constraint_name= :foreign_key_constraint_name

No comments: