SQL Server 清空所有表數據的三種場景完整解決方案

三種清空需求的遞進關係

實際開發中，清空數據庫表數據常遇到三種遞進的需求：

1. 基礎需求：僅清空所有表數據
2. 進階需求：清空數據 + 重置標識列（Identity）從 1 開始
3. 完整需求：清空數據 + 重置標識列 + 處理外鍵約束

場景一：僅清空數據（無約束或允許 TRUNCATE）

最簡單高效的方法是使用 TRUNCATE TABLE，它比 DELETE 更快、記錄日誌更少、自動重置標識列。

方法 A：游標遍歷（兼容性最好）

DECLARE @tableName VARCHAR(128), @sql NVARCHAR(MAX);
DECLARE cur CURSOR FOR
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE';

OPEN cur;
FETCH NEXT FROM cur INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'TRUNCATE TABLE ' + QUOTENAME(@tableName);
    EXEC sp_executesql @sql;
    FETCH NEXT FROM cur INTO @tableName;
END
CLOSE cur; DEALLOCATE cur;

方法 B：動態 SQL 批量生成（無游標，性能更好）

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(TABLE_NAME) + N';'
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';
EXEC sp_executesql @sql;

場景二：清空數據 + 重置標識列

關鍵點：TRUNCATE TABLE 本身就會重置標識列種子值。若因約束無法使用 TRUNCATE，需手動重置：

-- 先 DELETE 再重置
DELETE FROM TableName;
DBCC CHECKIDENT('TableName', RESEED, 0); -- 下一條插入從 1 開始

場景三：存在外鍵約束的完整解決方案

外鍵約束阻止 TRUNCATE，必須按拓撲順序操作：

1. 先處理無外鍵引用的表（葉子表） → 可用 TRUNCATE
2. 再處理被引用的表（父表） → 必須 DELETE + RESEED

無游標分批處理腳本（生產環境推薦）

SET NOCOUNT ON;
DECLARE @tableName SYSNAME, @sql NVARCHAR(MAX);

-- 第一輪：處理無外鍵或僅有主鍵的表（可 TRUNCATE）
WHILE EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES T
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
        ON T.TABLE_NAME = TC.TABLE_NAME AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
    WHERE T.TABLE_TYPE = 'BASE TABLE'
      AND T.TABLE_NAME NOT IN ('dtproperties', 'sysconstraints', 'syssegments')
      AND (TC.CONSTRAINT_TYPE IS NULL OR TC.CONSTRAINT_TYPE = 'PRIMARY KEY')
      AND T.TABLE_NAME > @tableName
)
BEGIN
    SELECT @tableName = MIN(T.TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES T
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
        ON T.TABLE_NAME = TC.TABLE_NAME AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
    WHERE T.TABLE_TYPE = 'BASE TABLE'
      AND T.TABLE_NAME NOT IN ('dtproperties', 'sysconstraints', 'syssegments')
      AND (TC.CONSTRAINT_TYPE IS NULL OR TC.CONSTRAINT_TYPE = 'PRIMARY KEY')
      AND T.TABLE_NAME > @tableName;

    SET @sql = N'TRUNCATE TABLE ' + QUOTENAME(@tableName);
    PRINT @sql; EXEC sp_executesql @sql;
END

-- 第二輪：處理有外鍵被引用的表（必須 DELETE + RESEED）
SET @tableName = '';
WHILE EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES T
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
        ON T.TABLE_NAME = TC.TABLE_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
    WHERE T.TABLE_TYPE = 'BASE TABLE'
      AND T.TABLE_NAME <> 'dtproperties'
      AND T.TABLE_NAME > @tableName
)
BEGIN
    SELECT @tableName = MIN(T.TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES T
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
        ON T.TABLE_NAME = TC.TABLE_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
    WHERE T.TABLE_TYPE = 'BASE TABLE'
      AND T.TABLE_NAME <> 'dtproperties'
      AND T.TABLE_NAME > @tableName;

    -- DELETE 數據
    SET @sql = N'DELETE FROM ' + QUOTENAME(@tableName);
    PRINT @sql; EXEC sp_executesql @sql;

    -- 檢查並重置標識列
    IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @tableName
          AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(@tableName)), 
                             COLUMN_NAME, 'IsIdentity') = 1
    )
    BEGIN
        SET @sql = N'DBCC CHECKIDENT(' + QUOTENAME(@tableName, '''') + N', RESEED, 0)';
        PRINT @sql; EXEC sp_executesql @sql;
    END
END
SET NOCOUNT OFF;

替代方案：暫時禁用約束

若表數量較多且結構穩定，可臨時禁用所有約束：

-- 禁用所有約束
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
-- 執行 TRUNCATE
EXEC sp_msforeachtable 'TRUNCATE TABLE ?';
-- 重新啟用約束
EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL';

注意：sp_msforeachtable 為非文檔化存儲過程，生產環境謹慎使用；啟用約束時會驗證現有數據，若數據違反約束會報錯。

最佳實踐建議

• 開發/測試環境：優先使用腳本重建數據庫（生成腳本 → 執行），最乾淨可靠
• 生產環境謹慎操作：必須備份、在事務中測試、確認業務停機窗口
• 保留種子數據：字典表、配置表等基礎數據不應清空，需在腳本中排除
• 記錄操作日誌：腳本中加入 PRINT 輸出執行的語句，便於審計與排查