我正在尋找一個 SQL 腳本，該腳本將列出從表 X 中洗掉記錄時將受到影響的所有表。它還應該列出在“樹”下受影響的依賴表，因為受影響的表將對其他表進行級聯洗掉，這反過來會影響其他人等。
uj5u.com熱心網友回復：
您可以使用遞回 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;
這將輸出如下內容：
| 基表 | 洗掉 |
|---|---|
| T2 | T2 --> T1 |
| T3 | T3 --> T2 --> T1 |
| T4 | T4 --> T3 --> T2 --> T1 |
| T5 | T5 --> T4 --> T3 --> T2 --> T1 |
db<>fiddle 上的示例
uj5u.com熱心網友回復：
-- 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/435013.html