Find DEPENDENT FOREIGN KEY CONSTRAINTS of a PRIMARY KEY TABLE with COLUMN NAME

Find DEPENDENT FOREIGN KEY CONSTRAINTS of a PRIMARY KEY TABLE with COLUMN NAME



COLUMN OWNER FORMAT A9 heading "Owner"
COLUMN CONSTRAINT_NAME FORMAT A30 heading "Constraint|Name"
COLUMN R_CONSTRAINT_NAME FORMAT A30 heading "Referenced|Constraint|Name"
COLUMN DELETE_RULE FORMAT A9 heading "Del|Rule"
COLUMN TABLE_NAME FORMAT A18 heading "Table Name"
COLUMN COLUMN_NAME FORMAT A30 heading "Column Name"
COLUMN CONSTRAINT_TYPE FORMAT A4 heading "Type"
COLUMN POSITION ALIAS POS
COLUMN POSITION 9999 heading "Pos"
COLUMN POSITION FORMAT 9999 heading "Pos"
BREAK ON CONSTRAINT_NAME SKIP PAGE



SELECT COL.OWNER,
       COL.CONSTRAINT_NAME,
       COL.COLUMN_NAME,
       COL.POSITION,
--     CON.CONSTRAINT_TYPE
DECODE (CON.CONSTRAINT_TYPE,
       'P','primary','R','foreign','U','unique','C','check') "Type"
FROM   DBA_CONS_COLUMNS COL,
       DBA_CONSTRAINTS CON
WHERE  COL.OWNER = upper('&&owner')
AND    COL.TABLE_NAME = upper('&&table')
AND    CONSTRAINT_TYPE <> 'R'
AND    COL.OWNER = CON.OWNER
AND    COL.TABLE_NAME = CON.TABLE_NAME
AND    COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
ORDER BY COL.CONSTRAINT_NAME, COL.POSITION;