This will give all the tables whose constraints reference a given table
/*
All the tables that reference a given table in their constraints.
*/
SELECT
a1.table_name "Target Table"
,a1.constraint_name "Target Constraint"
,d1.column_name "Target Column"
,b1.table_name "Referencing Table"
,b1.constraint_name "Referencing Constraint"
,c1.column_name "Referencing Column"
FROM DBA_CONS_COLUMNS C1
, DBA_CONSTRAINTS A1
, DBA_CONSTRAINTS b1
,DBA_CONS_COLUMNS d1
where
a1.table_name=d1.table_name and
C1.Table_name=b1.table_name and
d1.constraint_name = a1.constraint_name and
C1.Constraint_Name = b1.constraint_name and
b1.constraint_name = a1.r_constraint_name and
A1.table_name =UPPER('&TAB') AND a1.owner=UPPER('&OWNER')
order by 3,4;
PILOTTO Diego
TECSIS To: Multiple recipients of list ORACLE-L
<TCSPIL <[EMAIL PROTECTED]>
@SIDERAR.COM> cc:
Sent by: root Subject: RE: Constraints Lookup
04/23/2002
06:23 PM
Please
respond to
ORACLE-L
Try this....
SELECT a1.constraint_name NAME,
DECODE ( a1.CONSTRAINT_TYPE, 'C', 'Check', 'P', 'Primary Key',
'R', 'Referential Integrity', 'U', 'Unique Key', 'V', 'Check Option on a
view') TYPE ,
a1.r_constraint_name RNAME, a1.status, a1.delete_rule,
c1.column_name, c1.position, R_CONSTRAINT_NAME, R_OWNER ,
a1.search_condition CONSTEXT
FROM USER_CONS_COLUMNS C1, USER_CONSTRAINTS A1
WHERE a1.CONSTRAINT_TYPE = 'R' -----Only Ref. Const.
AND C1.Table_name=A1.table_name
AND C1.Constraint_Name = A1.constraint_name
AND C1.owner = A1.owner
AND A1.table_name =&TAB
AND a1.owner=&OWNER
ORDER BY 1,7
-----Original Message-----
Sent: Tuesday, April 23, 2002 5:53 PM
To: Multiple recipients of list ORACLE-L
Yes, this should be easy....
I need to find all the tables that referance a given table in their
constraints. Can somebody help, it's been a long day....
TIA,
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Weatherman
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: PILOTTO Diego TECSIS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
