SQL Server 一鍵清空所有資料表並重置識別欄位：三種方法效能比較

業務場景

測試環境重置、資料遷移前清理、定期歸檔後清空。需求：清空所有使用者表、重置 Identity 種子、保留結構描述/索引/約束/權限。

方法 1：動態 SQL 字串拼接（小表量適用）

DECLARE @SQL NVARCHAR(MAX) = N''
SELECT @SQL += N'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N';'
FROM sys.tables
WHERE is_ms_shipped = 0
EXEC sp_executesql @SQL

⚠️ 限制：NVARCHAR(MAX) 理論 2GB，但極大量表（> 5000）可能超過批次大小或執行逾時。

方法 2：游標逐一執行（穩健、可控、可記錄）

DECLARE @TableName SYSNAME, @SchemaName SYSNAME, @SQL NVARCHAR(500)
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT QUOTENAME(SCHEMA_NAME(schema_id)), QUOTENAME(name)
    FROM sys.tables
    WHERE is_ms_shipped = 0
    ORDER BY schema_id, name

OPEN Cur
FETCH NEXT FROM Cur INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'TRUNCATE TABLE ' + @SchemaName + N'.' + @TableName
    BEGIN TRY
        EXEC sp_executesql @SQL
        PRINT 'Truncated: ' + @SchemaName + N'.' + @TableName
    END TRY
    BEGIN CATCH
        PRINT 'FAILED: ' + @SchemaName + N'.' + @TableName + N' - ' + ERROR_MESSAGE()
    END CATCH
    FETCH NEXT FROM Cur INTO @SchemaName, @TableName
END
CLOSE Cur
DEALLOCATE Cur

優點：單表失敗不影響後續、可記錄日誌、可加條件篩選（如特定結構描述）。

方法 3：未文件化程序 sp_MSforeachtable（最簡潔、不可篩選）

EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

⚠️ 風險：Microsoft 未承諾支援、未來版本可能移除、無法 TRY/CATCH 個別表、無法篩選系統表/特定結構描述。

TRUNCATE vs DELETE 關鍵差異

外鍵相依處理：拓撲排序清空

-- 1. 停用所有外鍵
DECLARE @SQL NVARCHAR(MAX) = N''
SELECT @SQL += N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
    + N' NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';'
FROM sys.foreign_keys fk
WHERE fk.is_ms_shipped = 0
EXEC sp_executesql @SQL

-- 2. 執行清空 (任選上述三法)

-- 3. 重新啟用外鍵 (WITH CHECK 確保現有資料符合)
SELECT @SQL = N''
SELECT @SQL += N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
    + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';'
FROM sys.foreign_keys fk
WHERE fk.is_ms_shipped = 0
EXEC sp_executesql @SQL

完整可重用儲存程序

CREATE PROCEDURE dbo.TruncateAllUserTables
    @SchemaFilter SYSNAME = NULL,  -- NULL 表示所有結構描述
    @PrintOnly BIT = 0             -- 1 僅列印不執行
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @SQL NVARCHAR(MAX) = N''
    
    -- 停用 FK
    SELECT @SQL += N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
        + N' NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';'
    FROM sys.foreign_keys fk
    WHERE fk.is_ms_shipped = 0
      AND (@SchemaFilter IS NULL OR SCHEMA_NAME(fk.schema_id) = @SchemaFilter)
    IF @PrintOnly = 1 PRINT @SQL ELSE EXEC sp_executesql @SQL

    -- 清空表
    SET @SQL = N''
    SELECT @SQL += N'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name) + N';'
    FROM sys.tables t
    WHERE t.is_ms_shipped = 0
      AND (@SchemaFilter IS NULL OR SCHEMA_NAME(t.schema_id) = @SchemaFilter)
    IF @PrintOnly = 1 PRINT @SQL ELSE EXEC sp_executesql @SQL

    -- 啟用 FK
    SET @SQL = N''
    SELECT @SQL += N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
        + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';'
    FROM sys.foreign_keys fk
    WHERE fk.is_ms_shipped = 0
      AND (@SchemaFilter IS NULL OR SCHEMA_NAME(fk.schema_id) = @SchemaFilter)
    IF @PrintOnly = 1 PRINT @SQL ELSE EXEC sp_executesql @SQL
END

呼叫範例

-- 預覽將執行的指令
EXEC dbo.TruncateAllUserTables @PrintOnly = 1

-- 實際執行
EXEC dbo.TruncateAllUserTables

注意事項

• 生產環境嚴禁直接執行，需完整備份、變更管控流程
• TRUNCATE 需 ALTER 權限，非 DELETE 權限
• 記憶體優化表、異度表、外部表不支援 TRUNCATE
• 異動複寫發佈表不可 TRUNCATE
• 執行前確認無長跑交易持有鎖定