SQL Server 批量清空指定資料庫所有資料：保留結構、重設種子、自動處理外鍵級聯

適用場景

專案交付前需清除測試資料、保留結構；或定期重置測試環境。表數量大時手工 TRUNCATE/DELETE 易遺漏、外鍵阻礙。本腳本三階段自動化：1. 將所有外鍵改為 CASCADE DELETE → 2. 逐表 DELETE 並 RESEED 身分識別欄 → 3. 還原外鍵原始刪除規則，全程含交易保護與錯誤記錄。

核心邏輯流程

1. 收集所有外鍵中繼資料 至表變數 @AllRelationships (含原始 DeleteRule)。
2. 階段一：游標遍歷各表，動態 SQL DROP CONSTRAINT ... ADD CONSTRAINT ... ON DELETE CASCADE，交易包裹，失敗回滾並記錄。
3. 階段二：再次遍歷，DELETE FROM table; DBCC CHECKIDENT (table, RESEED, 0) 重設種子為 0。
4. 階段三：依 @AllRelationships 記錄的原始 DeleteRule (NO_ACTION/CASCADE/SET_NULL/SET_DEFAULT) 還原外鍵定義。

關鍵代碼片段

外鍵中繼資料收集

DECLARE @AllRelationships TABLE (
    ForeignKey VARCHAR(512),
    TableName VARCHAR(512),
    ColumnName VARCHAR(512),
    ReferenceTableName VARCHAR(512),
    ReferenceColumnName VARCHAR(512),
    DeleteRule VARCHAR(512)
);

INSERT INTO @AllRelationships
SELECT f.name,
       OBJECT_NAME(f.parent_object_id),
       COL_NAME(fc.parent_object_id, fc.parent_column_id),
       OBJECT_NAME(f.referenced_object_id),
       COL_NAME(fc.referenced_object_id, fc.referenced_column_id),
       delete_referential_action_desc
FROM sys.foreign_keys f
JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id;

階段一：改為 CASCADE (節錄)

DECLARE DataBaseTables0 CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t;

OPEN DataBaseTables0;
FETCH NEXT FROM DataBaseTables0 INTO @TableOwner, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (SELECT 1 FROM @IgnoreTables WHERE TableName = @TableName)
    BEGIN
        DECLARE DataBaseTableRelationships CURSOR FOR
        SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName
        FROM @AllRelationships WHERE TableName = @TableName;
        -- ... 迴圈內動態 SQL ALTER TABLE ... ON DELETE CASCADE ...
    END
    FETCH NEXT FROM DataBaseTables0 INTO @TableOwner, @TableName;
END

忽略表設定

DECLARE @IgnoreTables TABLE (TableName VARCHAR(512));
INSERT INTO @IgnoreTables VALUES ('sysdiagrams');  -- 可自行擴充

完整腳本特色

• 三階段分離：結構變更 → 資料清理 → 結構還原，職責單一。
• 交易保護每個 ALTER：單一外鍵變更失敗不影響其他。
• 錯誤詳細記錄：輸出錯誤號、訊息、嘗試的 SQL，便於事後補救。
• RESEED 歸零：身分識別欄從 1 重新開始，測試資料更乾淨。
• 可擴充忽略清單：保護特定表 (如設定表、字典表) 不被清空。

執行前檢查清單

1. 確認目標資料庫無關鍵生產資料。
2. 備份資料庫 (或至少結構腳本)。
3. 檢視 @IgnoreTables 是否需新增保護表。
4. 在非生產環境先行驗證。

總結

此腳本將「清空所有資料並重設種子」從繁瑣手工操作轉為可重複、可審計、可擴充的自動化流程，極大降低交付前準備與測試環境重置的時間成本與風險。