SQL Server 快速清空所有資料表資料的三種實戰方案（含外鍵約束處理）

場景痛點

開發/測試環境常需「一鍵清空資料庫」。但實際表間存在外鍵約束，單純 DELETE 或 TRUNCATE 會因約束衝突失效，且逐表手動處理極其繁瑣。

方案一：sp_MSforeachtable + TRUNCATE（無約束或已禁用約束）

-- 僅適用於無外鍵約束的表
EXEC sp_MSforeachtable @Command1 = 'TRUNCATE TABLE ?';

優點：TRUNCATE 速度極快、記錄 log 極少、釋放頁面徹底。
缺點：有外鍵時直接報錯。

方案二：禁用約束 → 刪除資料 → 重新啟用（通用最稳）

CREATE PROCEDURE sp_DeleteAllData
AS
BEGIN
    SET NOCOUNT ON;
    -- 1. 禁用所有約束與觸發器
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL';
    
    -- 2. 清空資料（DELETE 可觸發觸發器，若不需觸發器可改 TRUNCATE）
    EXEC sp_MSForEachTable 'DELETE FROM ?';
    
    -- 3. 重新啟用
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
    EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL';
    
    -- 4. 驗證（可選）
    EXEC sp_MSFOREACHTABLE 'SELECT COUNT(*) AS Rows FROM ?';
END
GO

關鍵點：sp_MSForEachTable 是未文檔化但廣泛使用的系統預存程序，可遍歷所有使用者表。生產環境執行前務必備份。

方案三：TRUNCATE TABLE 深度解析（單表最優）

對單表清空，TRUNCATE TABLE 優於 DELETE：

• Log 極少：只記錄頁釋放，不逐行記錄
• 鎖較少：鎖定表/頁，不逐行加鎖
• 無殘留頁：執行後不留空頁，DELETE 需額外表鎖才能釋放堆表空頁
• 保留結構：索引、約束、觸發器、權限均保留

限制：被外鍵參考的表不可 TRUNCATE；不觸發 DELETE 觸發器；無法用於發行發佈的表。

總結建議