Get all foreign keys and their cascade actions in SQL Server

Cascading deletes and updates are not for everyone and often they can cause problems or are better alternatives to using them. If you do use them and want to confirm what the update and delete actions are, the following query will do the trick to find all foreign keys in SQL Server along with their cascade actions: 

SELECT name, update_referential_action_desc, delete_referential_action_desc
FROM sys.foreign_keys

One thought on “Get all foreign keys and their cascade actions in SQL Server”

  1. Just reading here:

    If you want to get the names of the tables and columns involved the following will work:

    SELECT AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id

Leave a Reply

Your email address will not be published. Required fields are marked *