Resolved: TSQL: Get all tables affected by Cascade Delete


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.


You can use a recursive CTE to generate a full hierarchy of tables affected, e.g.
WITH OnDelete AS
(   SELECT f.parent_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'
    SELECT  od.parent_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
        (   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:
BaseTable OnDelete
T2 T2 –> T1
T3 T3 –> T2 –> T1
T4 T4 –> T3 –> T2 –> T1
T5 T5 –> T4 –> T3 –> T2 –> T1

Example on db<>fiddle

If you have better answer, please add a comment about this, thank you!