Oracle constraint verification

constraint-verification.sql

-- SQL command

SELECT CPAI.OWNER, CPAI.CONSTRAINT_NAME, CPAI.CONSTRAINT_TYPE, CPAI.TABLE_NAME,
CPAI.STATUS, CPAI.R_CONSTRAINT_NAME, COL.COLUMN_NAME,
(
  SELECT F.TABLE_NAME FROM DBA_CONSTRAINTS F
  WHERE F.CONSTRAINT_NAME = CPAI.R_CONSTRAINT_NAME
) TABLE_NAME_FILHA
FROM DBA_CONSTRAINTS CPAI, DBA_CONS_COLUMNS COL
WHERE CPAI.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
  AND CPAI.OWNER = '&PROPRIETARIO'
  AND CPAI.TABLE_NAME = '&TABELA'
  AND CPAI.CONSTRAINT_TYPE = 'R'
GROUP BY CPAI.OWNER, CPAI.CONSTRAINT_NAME, CPAI.CONSTRAINT_TYPE, 
         CPAI.TABLE_NAME, CPAI.STATUS, CPAI.R_CONSTRAINT_NAME, COL.COLUMN_NAME
ORDER BY COL.COLUMN_NAME;