Query to find all FK constraints and their delete rules (SQL Server)

Question

In SQL Server 2005, can I issue an SQL query to list all FK constraints on tables within the DB, and show the delete rule? (ie nothing, cascade, set null, or set default)

The output I'm looking for is something akin to:

FK_NAME                  ON_DELETE
==================================
FK_LINEITEM_STATEMENT    CASCADE
FK_ACCOUNTREP_CLIENT     NOTHING

score 44 · Accepted Answer · answered Oct 06 '10 at 23:09

44

You can try this:

SELECT name, delete_referential_action_desc
FROM sys.foreign_keys

answered Oct 06 '10 at 23:09

bobs

21,844
12
67
78

1

Thank you. I was doing things the MS way and double-clicking my way to RSI before you provided this little nugget. – Synesso Oct 06 '10 at 23:14

score 10 · Answer 2 · edited Feb 07 '18 at 17:53

10

Little late to the game here, but you might also try this:

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

edited Feb 07 '18 at 17:53

Undo

25,519
37
106
129

answered Mar 30 '11 at 21:46

Brian

1,383
3
16
30

score 1 · Answer 3 · edited Feb 07 '18 at 17:54

1

You can use also expression in the WHERE block:

objectproperty(object_id('FK_your_constraint_name'), 'CnstIsDeleteCascade')

or

objectproperty(your_constraint_object_id, 'CnstIsDeleteCascade')

edited Feb 07 '18 at 17:54

Undo

25,519
37
106
129

answered Apr 21 '16 at 13:16

Alexey

11
1

Query to find all FK constraints and their delete rules (SQL Server)

3 Answers3

Linked