Finding all constraints and their affected columns in an Oracle database

Found an SQL script to list all constraints in an Oracle database together with affected columns. Putting it here in case I need it again… Took a while to run, but sure beats having to look through all the table definitions to find what exactly is preventing me from deleting a row…

SELECT UC.OWNER
,      UC.CONSTRAINT_NAME
,      UCC1.TABLE_NAME||'.'||UCC1.COLUMN_NAME "CONSTRAINT_SOURCE"
,      'REFERENCES'
,      UCC2.TABLE_NAME||'.'||UCC2.COLUMN_NAME "REFERENCES_COLUMN"
FROM USER_CONSTRAINTS uc
,    USER_CONS_COLUMNS ucc1
,    USER_CONS_COLUMNS ucc2
WHERE UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
  AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
  AND UCC1.POSITION = UCC2.POSITION -- Correction for multiple column primary keys.
  AND UC.CONSTRAINT_TYPE = 'R'
ORDER BY UCC1.TABLE_NAME
,        UC.CONSTRAINT_NAME;

If you’re just looking for one particular constraint you can of course also add and UC.CONSTRAINT_NAME = 'SOME NAME' 🙂

  • Keshab Pandey

    Its working Thank you so much…..