Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Meta query to get FK to PK reference constraints
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
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
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