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.COMcc:
Sent by: rootSubject: 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 DiegoTECSIS
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
