Re: retrieving foreign key info

From: <nelsona_at_my-dejanews.com>
Date: Thu, 25 Jun 1998 18:29:30 GMT
Message-ID: <6mu4u9$ohm$1@nnrp1.dejanews.com>

In article <3591408B.760C15CB_at_wave.netREMOVE_THIS>,   Jim Haran <jharan_at_wave.netREMOVE_THIS> wrote:
>
> Hi all,
> I'm using Oracle 8 on NT4 and my question is this: Is there a system
> table I can query to find out foreign key info.
> For example: I want to find out what table and column the
> department id column in a personnel table references. Any help is very
> much appreciated.
>
> Jim
>
>

Try this:

SELECT DISTINCT DBA_CONSTRAINTS_1.OWNER, DBA_CONSTRAINTS_1.CONSTRAINT_NAME,

DBA_CONSTRAINTS_1.TABLE_NAME, DBA_CONS_COLUMNS.COLUMN_NAME,
DBA_CONS_COLUMNS.POSITION, DBA_CONSTRAINTS_1.R_OWNER,
DBA_CONSTRAINTS_1.R_CONSTRAINT_NAME, DBA_CONSTRAINTS.TABLE_NAME,
DBA_CONS_COLUMNS_1.COLUMN_NAME, DBA_CONS_COLUMNS_1.POSITION FROM

SYS.DBA_CONS_COLUMNS DBA_CONS_COLUMNS, SYS.DBA_CONS_COLUMNS

DBA_CONS_COLUMNS_1, SYS.DBA_CONSTRAINTS DBA_CONSTRAINTS, SYS.DBA_CONSTRAINTS
DBA_CONSTRAINTS_1 WHERE DBA_CONSTRAINTS.CONSTRAINT_NAME =
DBA_CONSTRAINTS_1.R_CONSTRAINT_NAME AND DBA_CONSTRAINTS_1.CONSTRAINT_NAME =
DBA_CONS_COLUMNS.CONSTRAINT_NAME AND DBA_CONSTRAINTS_1.TABLE_NAME =
DBA_CONS_COLUMNS.TABLE_NAME AND DBA_CONSTRAINTS_1.R_CONSTRAINT_NAME =
DBA_CONS_COLUMNS_1.CONSTRAINT_NAME AND DBA_CONSTRAINTS.TABLE_NAME =
DBA_CONS_COLUMNS_1.TABLE_NAME and DBA_CONS_COLUMNS.POSITION =
DBA_CONS_COLUMNS_1.POSITION AND DBA_CONSTRAINTS.OWNER Not Like 'SYS%' ORDER

BY DBA_CONSTRAINTS_1.OWNER, DBA_CONSTRAINTS_1.TABLE_NAME, DBA_CONS_COLUMNS.COLUMN_NAME -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Jun 25 1998 - 13:29:30 CDT