Query to find all FK constraints and their delete rules (SQL Server)
12,748
Solution 1
You can try this:
SELECT name, delete_referential_action_desc
FROM sys.foreign_keys
Solution 2
Little late to the game here, but you might also try this:
select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Author by
Synesso
I first started programming BASIC and rudimentary machine language on my Commodore 16 in the 1980s. These days I work remotely from the beautiful Gold Coast in several different languages - mainly Scala, Rust & Go. My passions are Jazz, Coffee and learning 日本語. If I have been helpful, please thank me by buying a coffee for a stranger.
Updated on June 06, 2022Comments
-
Synesso almost 2 years
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
-
Synesso over 13 yearsThank you. I was doing things the MS way and double-clicking my way to RSI before you provided this little nugget.