Re: How to check if a column is a foreign key? If yes, to which column

From: Marin Dimitrov <marin_at_sirma.bg>
Date: Tue, 1 Aug 2000 13:44:56 +0300
Message-Id: <10576.113464@fatcity.com>

• Original Message ----- From: Dries Verachtert <dries_at_ulyssis.org> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Tuesday, August 01, 2000 13:44 Subject: How to check if a column is a foreign key? If yes, to which column

>

> Can someone give me some hints on which tables i need to use or if you got
> lotsa time, a nice query wich gives me the columnname, the columnname in
the
> referenced table and the name of the referenced table of each foreignkey
in
> a table? :-)

copy&paste from OraSnap (http://www.stewartmc.com/oracle/orasnap/) :

---

select   c.OWNER,
         c.TABLE_NAME,
         c.CONSTRAINT_NAME,
         cc.COLUMN_NAME,
         r.TABLE_NAME,
         rc.COLUMN_NAME,
         cc.POSITION
from     dba_constraints c, dba_constraints r,
         dba_cons_columns cc, dba_cons_columns rc
where    c.CONSTRAINT_TYPE = 'R'
and      c.OWNER not in ('SYS','SYSTEM')
and      c.R_OWNER = r.OWNER
and      c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and      c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and      c.OWNER = cc.OWNER
and      r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and      r.OWNER = rc.OWNER
and      cc.POSITION = rc.POSITION

order by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION;

---

Of course replace the line

"and c.OWNER not in ('SYS','SYSTEM') "

with

"and c.OWNER = 'YOUR_OWNER' "

hth,

    Marin

---

"And it comes to be that the soothing light at the end of your tunnel Received on Tue Aug 01 2000 - 05:44:56 CDT