RE: Constraints Lookup

From: Thomas Day <tday6_at_csc.com>
Date: Wed, 24 Apr 2002 07:18:29 -0800
Message-ID: <F001.0044DDBE.20020424071829@fatcity.com>

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              <ORACLE-L_at_fatcity.com>                            
                    @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: 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
--------------------------------------------------------------------
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: Thomas Day
  INET: tday6_at_csc.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).

Received on Wed Apr 24 2002 - 10:18:29 CDT