Listing ON DELETE CASCADE Constraints in MS SQL Server

To check which tables have foreign key constraints with referential actions on other tables, use the sys.foreign_keys system view like this:

DECLARE @t NVARCHAR(128)
SET @t = 'My_Table_Name'

SELECT p.name, fk.delete_referential_action_desc, t.name
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON fk.parent_object_id = t.object_id
INNER JOIN sys.objects p ON fk.referenced_object_id = p.object_id
WHERE p.name = @t
ORDER BY 1, 3

If you want not only master-detail, but also grandparent-parent-child relations, you need a join over 3 tables and 2 foreign keys:

SELECT DISTINCT p.name, fk.delete_referential_action_desc, t.name, 
   fk2.delete_referential_action_desc, tt.name
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON fk.parent_object_id = t.object_id
INNER JOIN sys.objects p ON fk.referenced_object_id = p.object_id
INNER JOIN sys.foreign_keys fk2 ON fk2.referenced_object_id = t.object_id
INNER JOIN sys.objects tt ON fk2.parent_object_id = tt.object_id
WHERE p.name = @t
AND t.object_id <> p.object_id
ORDER BY 1, 3, 5

The filter on t.object_id <> p.object_id exludes circular relations in the first level which show up in the second level anyway.

Advertisements