Re: retrieve foreign keys list

From: Doug O'Leary <dkoleary_at_mediaone.net>
Date: Sat, 21 Jul 2001 21:52:40 GMT
Message-ID: <MPG.1592e26817f0ffca98979c@news.randori.com>

Hey;

You can try something like:

---

column "Foreign Key" format a15
column "Table Name" format a13
column "Refed Const" format a15

column column_name heading "Column Name" format a13

select a.constraint_name "Foreign Key",

   b.table_name "Table Name",
   b.constraint_name "Refed Const",
   c.column_name

from user_constraints a,

   user_constraints b,
   user_cons_columns c
where a.r_constraint_name = b.constraint_name and a.constraint_type = 'R'
and b.constraint_name = c.constraint_name order by a.constraint_name
/

clear columns

---

which, on my system results in:

SQL> @id_fk_refs

Foreign Key Table Name Refed Const Column Name --------------- ------------- --------------- ------------- CNT_CTYPE_FK CONTACT_TYPES CONTACT_TYPE_PK CT_TYPE

CNT_POC_FK      NAMES         NAMES_PK        KPOC
NAMES_KTYPE_FK  POC_TYPES     POCT_PK         KTYPE
ORDERS_CUST_FK  CUSTOMERS     CUST_PK         CUST_ID
POC_FK          NAMES         NAMES_PK        KPOC
WO_FK           CONTRACTS     WO_UNQ          WORK_ORDER

6 rows selected.

I don't know if that'll work from asp or not - not being familiar with asp at all. HTH, though.

Doug

-- 
-------------------
Douglas K. O'Leary
Senior System Administrator
dkoleary_at_mediaone.net

Received on Sat Jul 21 2001 - 16:52:40 CDT