Synesso Synesso - 7 months ago 18
SQL Question

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

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

Answer

You can try this:

SELECT name, delete_referential_action_desc
FROM sys.foreign_keys