Xref: alice comp.databases.oracle.server:39514
Path: alice!news-feed.fnsi.net!newsfeed.ecrc.net!news-fra.pop.de!news.hamburg.pop.de!not-for-mail
From: Rolf Hauck <Rolf.Hauck@datenrevision.de>
Newsgroups: comp.databases.oracle.server
Subject: Re: DBA Question About Foreign Keys
Date: Tue, 02 Feb 1999 08:35:55 +0100
Organization: datenrevision GmbH
Lines: 64
Message-ID: <36B6AADB.CA80DF3B@datenrevision.de>
References: <36B650DC.D5BF800E@exesolutions.com>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="------------7D13452807DAD63820AED337"
X-Mailer: Mozilla 4.05 [de]C-NECCK  (WinNT; I)
To: dmorgan@exesolutions.com
Cache-Post-Path: internetserver.datenrevision.de!unknown@pc117
Cache-Post-Path: ns.work.de!unknown@internetserver.datenrevision.de
X-Cache: nntpcache 2.3.3 (see http://www.nntpcache.org/)
--------------7D13452807DAD63820AED337
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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
--------------7D13452807DAD63820AED337
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Visitenkarte für Rolf Hauck
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Rolf Hauck
n:              ;Rolf Hauck
org:            datenrevision GmbH
email;internet: Rolf.Hauck@datenrevision.de
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard


--------------7D13452807DAD63820AED337--