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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/01/2002 02:08 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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;
lc
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lisa R. Clary
INET: [EMAIL PROTECTED]
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: [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:
INET: [EMAIL PROTECTED]
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: [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).
