TSQL:获取所有受级联删除影响的表

Question

我正在寻找一个SQL脚本，该脚本将列出从表X中删除记录时将受到影响的所有表。它还应该列出受影响的从属表；树；因为受影响的表将级联删除到其他表，这反过来又会影响其他表等。

Answer 1

您可以使用递归CTE生成受影响的表的完整层次结构，例如

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;

这将输出类似于：

基本表	OnDelete
T2	T2->T1
T3	T3-->T2->T1
T4	T4->T3->T2->T1
T5	T5-->T4->T3->T2->T1

-- 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