Oracle-L: RE: Constraints Lookup

From: PILOTTO Diego TECSIS <TCSPIL_at_SIDERAR.COM>
Date: Tue, 23 Apr 2002 14:23:23 -0800
Message-ID: <F001.0044D126.20020423142323@fatcity.com>

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: john.weatherman_at_replacements.com

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: ListGuru_at_fatcity.com (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: TCSPIL_at_SIDERAR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------