Wednesday, 16 March 2016

How to find Foreign Keys references to a Table

In order to find all the Foreign Keys that point to a certain Table, you can use the SQL bellow:

SELECT table_name,
  constraint_name,
  status,
  owner
FROM all_constraints
WHERE r_owner          = :owner
AND constraint_type    = 'R'
AND r_constraint_name IN
  (SELECT constraint_name
  FROM all_constraints
  WHERE constraint_type IN ('P', 'U')
  AND table_name         = :table_name
  AND owner              = :owner
  )
ORDER BY table_name,
  constraint_name ;

No comments:

Post a comment