模擬 DELETE CASCADE

我需要從一個表中刪除許多行，許多其他表將其引用為 FK。

如果我有 DELETE CASCADE，我可以從人員中刪除記錄，並自動從其他表中刪除，但我不喜歡這個想法。

我想要一個將 DELETE 語句作為輸入並輸出所有需要的 DELETE 語句的腳本。理想情況下，它會告訴我將刪除多少條記錄。

例子：

輸入：

DELETE FROM persons WHERE person_id < 1000000

輸出：

-- This would delete 124,345 records
DELETE FROM persons_addresses WHERE person_id < 1000000
-- This would delete 82,954 records
DELETE FROM persons_phone numbers WHERE person_id < 1000000
...
-- This would delete 999,999 records
DELETE FROM persons WHERE person_id < 1000000

生成查詢和計數所需的資訊都在目錄視圖中可用，例如sys.columns和sys.foreign_key_columns。我們需要找到所有的子表，然後計算每個子表中有多少行符合與父ID相同的條件。

CREATE PROCEDURE dbo.GeneratedPathedDeletes
 @ParentTable     nvarchar(512),
 @ParentColumn    nvarchar(128),
 @DeleteCriteria  nvarchar(255)
AS
BEGIN
 DECLARE @sql nvarchar(max) = N'',
         @src nvarchar(max) = N'SELECT ''DELETE $t$ WHERE $c$ $clause$;'' UNION ALL SELECT 
         ''-- This would delete '' + (SELECT RTRIM(COUNT(*)) FROM $t$ WHERE $c$ $clause$) 
         + '' rows.'';';

 SELECT @sql += REPLACE(REPLACE(REPLACE(@src,N'$t$',t),N'$c$',c),N'$clause$',@DeleteCriteria)
 FROM
 (
   SELECT t = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
          c = QUOTENAME(pc.name)
   FROM sys.foreign_key_columns AS pt
   INNER JOIN sys.columns AS pc
      ON pt.parent_object_id = pc.[object_id]
     AND pt.parent_column_id = pc.column_id
   INNER JOIN sys.columns AS rc
      ON pt.referenced_column_id = rc.column_id
     AND pt.referenced_object_id = rc.[object_id]
   WHERE pt.referenced_object_id = OBJECT_ID(@ParentTable)
     AND rc.name = @ParentColumn
 ) AS x;

 -- final delete of parent table:
 SELECT @sql += REPLACE(REPLACE(REPLACE(@src,N'$t$',@ParentTable),
   N'$c$',@ParentColumn),N'$clause$',@DeleteCriteria);

 EXEC sys.sp_executesql @sql;
END
GO

範例用法：

EXEC dbo.GeneratedPathedDeletes
    @ParentTable    = N'dbo.persons',
    @ParentColumn   = N'person_id',
    @DeleteCriteria = N' < 100000'; 

我的範例表要小得多，但我的輸出看起來像這樣：

-- This would delete 12 rows.
DELETE dbo.persons_addresses WHERE person_id < 1000000;

-- This would delete 4 rows.
DELETE dbo.persons_phone_numbers WHERE person_id < 1000000;

-- This would delete 2 rows.
DELETE dbo.persons WHERE person_id < 1000000;

限制：

• 這不會執行刪除 - 您仍然需要根據輸出手動執行這些刪除。
• 這不處理循環路徑或孫子；只有傳統的親子。
• 這不處理多列鍵，或者可能是自引用鍵（甚至沒有嘗試過）。
• 更複雜的子句，如 IN 子句或多個範圍，將需要更多的工作。
• 這對 SQL 注入不安全 - 如果您從使用者那裡接受武器並將它們傳遞到此過程中，請參閱我的提示第 1部分和第 2 部分。

引用自：https://dba.stackexchange.com/questions/247366