I'm looking for a SQL script that will list all tables that will be affected when deleting a record from table X. It should also list the dependent tables affected down the "tree" as the affected tables will have cascade deletes to other, which in turn will affect others etc.

CodePudding user response：

-- using sys tables to enumerate foreign keys
    SELECT
        f.name constraint_name
       ,OBJECT_NAME(f.parent_object_id) referencing_table_name
       ,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name
       ,OBJECT_NAME (f.referenced_object_id) referenced_table_name
       ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name
       ,delete_referential_action_desc
       ,update_referential_action_desc
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
       ON f.object_id = fc.constraint_object_id
    ORDER BY f.name

CodePudding user response：

You can use a recursive CTE to generate a full hierarchy of tables affected, e.g.

WITH OnDelete AS
(   SELECT f.parent_object_id,
            f.referenced_object_id,
            RecursionLevel = 1,
            ObjectTree = CONVERT(VARCHAR(MAX), CONCAT(OBJECT_NAME(f.parent_object_id), ' --> ', OBJECT_NAME(f.referenced_object_id)))
    FROM sys.foreign_keys AS f
    WHERE f.delete_referential_action_desc = 'CASCADE'
    UNION ALL
    SELECT  od.parent_object_id,
            f.referenced_object_id,
            od.RecursionLevel   1,
            ObjectTree = CONVERT(VARCHAR(MAX), CONCAT(od.ObjectTree, ' --> ', OBJECT_NAME(f.referenced_object_id)))
    FROM    OnDelete AS od
            INNER JOIN sys.foreign_keys AS f
                ON f.parent_object_id = od.referenced_object_id
                AND f.delete_referential_action_desc = 'CASCADE'

)
SELECT  BaseTable = OBJECT_NAME(od.parent_object_id),
        OnDelete = od.ObjectTree
FROM    OnDelete AS od
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    OnDelete AS ex
            WHERE   ex.parent_object_id = od.parent_object_id
            AND     ex.ObjectTree LIKE CONCAT(od.ObjectTree, '%')
            AND     LEN(ex.ObjectTree) > LEN(od.ObjectTree)
        )
ORDER BY od.parent_object_id;

This will output something like:

Example on db<>fiddle