Home > Scripts > How to get the details of foreign key information for a table.

How to get the details of foreign key information for a table.

October 31, 2013

Recently i came across to delete data from a table where it has many foreign constraints. So i followed below path and went fine.

set lines 120
set pages 120
column COLUMN_NAME format a20
column CONSTRAINT_NAME format a20
column TARGET_TABLE_NAME format a15
column TABLE_NAME FORMAT A15
COLUMN R_CONSTRAINT_NAME FORMAT A10
SELECT UC.TABLE_NAME,
UCC2.CONSTRAINT_NAME,
UCC2.COLUMN_NAME,
UCC.TABLE_NAME TARGET_TABLE_NAME,
UC.R_CONSTRAINT_NAME,
UCC.COLUMN_NAME
FROM (SELECT TABLE_NAME, CONSTRAINT_NAME, R_CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS) UC,
(SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM USER_CONS_COLUMNS) UCC,
(SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM USER_CONS_COLUMNS) UCC2
WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
AND uc.constraint_type = ‘R’
ORDER BY 1,2,3,4;

 

Advertisements
Categories: Scripts
%d bloggers like this: