Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Meta query to get FK to PK reference constraints

Meta query to get FK to PK reference constraints

From: <JoeBrain00_at_gmail.com>
Date: Fri, 21 Sep 2007 14:19:21 -0000
Message-ID: <1190384361.618221.196910@g4g2000hsf.googlegroups.com>


Thought this might be useful...

This Meta query returns the Primary Key / Foreign Key reference constraints for all tables...

select

b.FK_TABLE_SCHEMA,
b.FK_TABLE_NAME,
b.FK_COLUMN_NAME,
b.FK_ORDINAL_POSITION,
b.UQ_TABLE_SCHEMA,
b.UQ_TABLE_NAME,
a.COLUMN_NAME as UQ_COLUMN_NAME,
a.POSITION as UQ_ORDINAL_POSITION,
b.FK_CONSTRAINT_NAME,
b.UQ_CONSTRAINT_NAME

FROM ALL_CONS_COLUMNS a,
(select
UCC.OWNER 				as FK_TABLE_SCHEMA,
UCC.TABLE_NAME			as FK_TABLE_NAME,
UCC.CONSTRAINT_NAME		as FK_CONSTRAINT_NAME,
UC.CONSTRAINT_TYPE 		as FK_TYPE,
UCC.COLUMN_NAME			as FK_COLUMN_NAME,
UCC.POSITION            as FK_ORDINAL_POSITION,
UC2.OWNER				as UQ_TABLE_SCHEMA,
UC2.TABLE_NAME 			as UQ_TABLE_NAME,
UC2.CONSTRAINT_NAME 	as UQ_CONSTRAINT_NAME,
UC2.CONSTRAINT_TYPE 	as UQ_TYPE

from ALL_CONS_COLUMNS UCC, ALL_CONSTRAINTS UC, ALL_CONSTRAINTS UC2 where
(UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME and UC.R_CONSTRAINT_NAME =
UC2.CONSTRAINT_NAME and UC.CONSTRAINT_TYPE = 'R')) b where (a.CONSTRAINT_NAME = b.UQ_CONSTRAINT_NAME and a.POSITION = b.FK_ORDINAL_POSITION)
order by FK_TABLE_NAME, FK_ORDINAL_POSITION

Oracle doesn't have INFORMATION_SCHEMAS, so I had to derive this from the Oracle dictionary views.
Hopefully it helps someone else in the future... Received on Fri Sep 21 2007 - 09:19:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US