Oracle-L: Re: Help find pk dependencies 7.3.4 db

From: <Jared.Still_at_radisys.com>
Date: Wed, 02 Oct 2002 15:38:26 -0800
Message-ID: <F001.004DEC1B.20021002153826@fatcity.com>

Try this:

select

fk.constraint_name
, pk.table_name pk_table
, pk.constraint_name pk_constraint
, pkc.column_name pk_column

from

dba_constraints fk
, dba_constraints pk
, dba_cons_columns pkc

where fk.owner = 'PRJSTAT'

and fk.table_name = 'PROJECTS'
and fk.r_constraint_name = pk.constraint_name
and fk.r_owner = pk.owner

and pkc.owner = fk.owner
and pkc.constraint_name = pk.constraint_name order by 1,2,pkc.position
/

Jared

"Lisa R. Clary" <lisa_at_cog.ufl.edu>
Sent by: root_at_fatcity.com
10/01/2002 02:08 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Help find pk dependencies 7.3.4 db

I am trying to find out for a given table the column names for the parent table to which the referential integrity is built upon. for example, table B
has primary keys=id, date_exam that are a foreign keys to table a, which has variable name pt_id, date_start. This is the query to deliver the pieces
of information, but as soon as I remove the comment line (as I only want one
line per return), it becomes a run-away and chews up the temp space. I have
looked at this for so long that I am probably missing the obvious. Any thoughts?

select o.constraint_name ownerconstraint, o.table_name ownertable,r1.position, r1.column_name, r.constraint_name, r2.position, r2.column_name
from all_constraints o,

(select constraint_name, column_name, position from all_cons_columns )
r1,

      all_constraints r,
      (select constraint_name, column_name, position from

all_cons_columns)
r2

 where o.constraint_name=r1.constraint_name and
       o.constraint_type='R' and
       o.r_constraint_name  = r.constraint_name and
       r.constraint_name = r2.constraint_name and
 ---      r1.position= r2.position and
       o.table_name='NEURO_ASSESSMENT'

order by o.constraint_name, o.table_name;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lisa R. Clary
  INET: lisa_at_cog.ufl.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Oct 02 2002 - 18:38:26 CDT