SQL Server 一鍵清空所有資料表資料：sp_MSForEachTable 實戰

清空擁有外鍵約束的資料庫所有資料表，逐表刪除極易陷入「先刪子表再刪父表」的依賴地獄。利用微軟未公開的 sp_MSForEachTable 系統預存程序，可一次禁用所有約束、清空資料、再恢復約束。

核心邏輯

1. 禁用所有資料表的 CHECK CONSTRAINT。
2. 禁用所有 TRIGGER。
3. 執行 DELETE FROM 清空資料。
4. 重新啟用約束與觸發器。
5. （選擇性）驗證所有表皆為空。

完整預存程序

CREATE PROCEDURE sp_DeleteAllData
AS
BEGIN
    -- 1. 禁用所有約束
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

    -- 2. 禁用所有觸發器
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL';

    -- 3. 清空所有資料
    EXEC sp_MSForEachTable 'DELETE FROM ?';

    -- 4. 重新啟用約束
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';

    -- 5. 重新啟用觸發器
    EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL';

    -- 6. 驗證（可省略）
    EXEC sp_MSForEachTable 'SELECT COUNT(*) AS Rows FROM ?';
END
GO

執行方式

EXEC sp_DeleteAllData;

原理說明

• sp_MSForEachTable 會遍歷目前資料庫所有使用者資料表，將 ? 替換為表名執行動態 SQL。
• 雖未正式文件化，但自 SQL Server 2000 以來廣泛存在於各版本，實務極為穩定。
• NOCHECK CONSTRAINT 僅暫停驗證，不移除定義；CHECK CONSTRAINT 恢復驗證。

注意事項

• 僅適用於 清空資料（DELETE），不會重設 IDENTITY 種子（需 DBCC CHECKIDENT 或 TRUNCATE）。
• 大量資料時 DELETE 會產生大量記錄檔，考慮分批或使用 TRUNCATE（需先移除 FK）。
• 執行前務必完整備份，操作不可逆。