最簡單清空 SQL Server 資料庫所有資料的方法

痛點：外鍵約束導致刪除死循環

直接使用 DELETE 或 TRUNCATE 清空資料表時，外鍵約束會阻擋操作，且這兩指令一次只能處理單一資料表。手動排序刪除順序在表多、約束複雜時極不現實。

解決方案：禁用約束 → 刪除資料 → 重新啟用約束

利用微軟未公開但廣泛使用的 sp_MSForEachTable 系統存儲過程，批次對所有資料表執行操作。

完整腳本

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 * FROM ?';
END
GO

使用注意事項

• 嚴禁在生產環境直接執行！ 請先備份資料庫，於測試/開發環境驗證。
• sp_MSForEachTable 雖無官方文件，但在 SQL Server 2000 至 2019 皆穩定可用。
• 大型資料庫執行可能耗時較久，建議在維護時段進行。
• 若需保留種子資料，請修改腳本排除特定資料表。

原理說明

sp_MSForEachTable 內部遍歷 sysobjects 類型為 'U' 的使用者資料表，將 ? 替換為資料表名稱執行動態 SQL。此法繞過外鍵檢查，一次性清空全庫。

替代方案

• 刪除並重建資料庫（最快，但需重建權限、索引等）。
• 使用 SSIS 匯出空資料結構再匯入。
• 逐表 TRUNCATE 配合拓撲排序（需自行處理相依順序）。