Tuesday, September 11, 2012

Find all foreign keys that have cascade delete or update

PROBLEM:
How do you find all foreign keys that have cascade delete or update?

SOLUTION:
SELECT B.NAME AS TableName,
       A.NAME AS FKname,
       A.DELETE_REFERENTIAL_ACTION_DESC AS DeleteAction,
       A.UPDATE_REFERENTIAL_ACTION_DESC AS UpdateAction
FROM SYS.FOREIGN_KEYS A
JOIN SYS.TABLES B ON A.PARENT_OBJECT_ID = B.OBJECT_ID
WHERE A.DELETE_REFERENTIAL_ACTION=1
  OR A.UPDATE_REFERENTIAL_ACTION=1
ORDER BY 1,
         2

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

No comments:

Post a Comment