Re: DBA Question About Foreign Keys

From: Rolf Hauck <Rolf.Hauck_at_datenrevision.de>
Date: Tue, 02 Feb 1999 08:35:55 +0100
Message-ID: <36B6AADB.CA80DF3B@datenrevision.de>

Try this:

select

const1.table_name,
cols1.column_name , 
const2.table_name ReferencedTable,
cols2.column_name , 

const1.delete_rule On_Delete,
cols1.position pos
from
user_cons_columns cols1, user_constraints const1, user_cons_columns cols2, user_constraints const2 where
const1.table_name = cols1.table_name

and const1.constraint_name = cols1.constraint_name
and const1.constraint_type = 'R'
and const1.r_constraint_name = const2.constraint_name
and const2.constraint_name = cols2.constraint_name
and const2.table_name = cols2.table_name

and cols1.position = cols2.position
and const2.table_name = 'YourTable' <-- 'X'

Rolf
order by 3,6,2

Dan Morgan schrieb:
>
> Can anyone please email me with the names of any tables or views that
> contain the names of tables that use a foreign key. I know it is in
> there somewhere but I just can't find it.
>
> Specifically, table X has a uniqe index key named X_UK on field X. I can
> easily find this information in the views dba_constraints and/or
> dba_indexes, and in the underlying tables. But how do I find the names
> of the tables that use/reference this unique index as a foreign key.
>
> Thanks,
>
> Daniel A. Morgan

Received on Tue Feb 02 1999 - 01:35:55 CST