萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 一鍵清空指定資料庫所有表資料(保留結構、重設種子、處理 FK)

適用場景

專案交付前清除測試資料、測試環境重置、資料遷移前準備空資料庫。需處理外鍵順序、Identity 種子重設、排除系統圖表表。

核心邏輯三階段

  1. 暫時將所有 FK 刪除規則改為 CASCADE,確保父表刪除時自動清除子表,避免 FK 阻擋。
  2. 逐表 DELETE + DBCC CHECKIDENT RESEED,清空資料並重設 Identity 從 1 開始。
  3. 還原 FK 原始刪除規則(NO ACTION / CASCADE / SET NULL / SET DEFAULT),保持結構不變。

完整腳本(已修正語法、加入錯誤處理)

SET NOCOUNT ON;

-- 1. 忽略表清單
DECLARE @IgnoreTables TABLE (TableName SYSNAME);
INSERT INTO @IgnoreTables VALUES ('sysdiagrams');

-- 2. 收集所有 FK 資訊
DECLARE @AllFKs TABLE (
    FKName SYSNAME, TableName SYSNAME, ColName SYSNAME,
    RefTableName SYSNAME, RefColName SYSNAME, DeleteRule SYSNAME
);
INSERT INTO @AllFKs
SELECT f.name, OBJECT_NAME(f.parent_object_id), COL_NAME(fc.parent_object_id, fc.parent_column_id),
       OBJECT_NAME(f.referenced_object_id), COL_NAME(fc.referenced_object_id, fc.referenced_column_id),
       delete_referential_action_desc
FROM sys.foreign_keys f
JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id;

-- 3. 階段一:改為 CASCADE
DECLARE @Schema SYSNAME, @Table SYSNAME, @FK SYSNAME, @Col SYSNAME, @RefTable SYSNAME, @RefCol SYSNAME, @DelRule SYSNAME;
DECLARE curTables CURSOR LOCAL FAST_FORWARD FOR
SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t;
OPEN curTables;
FETCH NEXT FROM curTables INTO @Schema, @Table;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (SELECT 1 FROM @IgnoreTables WHERE TableName = @Table)
    BEGIN
        PRINT '['+@Schema+'].['+@Table+']';
        DECLARE curFKs CURSOR LOCAL FAST_FORWARD FOR
        SELECT FKName, ColName, RefTableName, RefColName FROM @AllFKs WHERE TableName = @Table;
        OPEN curFKs;
        FETCH NEXT FROM curFKs INTO @FK, @Col, @RefTable, @RefCol;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                EXEC(N'
                    ALTER TABLE ['+@Schema+'].['+@Table+'] DROP CONSTRAINT '+@FK+';
                    ALTER TABLE ['+@Schema+'].['+@Table+'] ADD CONSTRAINT '+@FK+'
                        FOREIGN KEY ('+@Col+') REFERENCES '+@RefTable+' ('+@RefCol+') ON DELETE CASCADE;');
                PRINT '  ==> '+@FK+' set CASCADE';
            END TRY
            BEGIN CATCH
                PRINT '  ==> FAIL '+@FK+': '+ERROR_MESSAGE();
            END CATCH
            FETCH NEXT FROM curFKs INTO @FK, @Col, @RefTable, @RefCol;
        END
        CLOSE curFKs; DEALLOCATE curFKs;
    END
    FETCH NEXT FROM curTables INTO @Schema, @Table;
END
CLOSE curTables; DEALLOCATE curTables;

-- 4. 階段二:刪除資料並重設種子
DECLARE curTables2 CURSOR LOCAL FAST_FORWARD FOR
SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t;
OPEN curTables2;
FETCH NEXT FROM curTables2 INTO @Schema, @Table;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (SELECT 1 FROM @IgnoreTables WHERE TableName = @Table)
    BEGIN
        PRINT 'Deleting ['+@Schema+'].['+@Table+']';
        BEGIN TRY
            EXEC(N'DELETE FROM ['+@Schema+'].['+@Table+']; DBCC CHECKIDENT (''['+@Table+']'', RESEED, 0);');
        END TRY
        BEGIN CATCH
            PRINT '  ==> DELETE FAIL: '+ERROR_MESSAGE();
        END CATCH
    END
    FETCH NEXT FROM curTables2 INTO @Schema, @Table;
END
CLOSE curTables2; DEALLOCATE curTables2;

-- 5. 階段三:還原 FK 規則
DECLARE curTables3 CURSOR LOCAL FAST_FORWARD FOR
SELECT SCHEMA_NAME(t.schema_id), t.name FROM sys.tables t;
OPEN curTables3;
FETCH NEXT FROM curTables3 INTO @Schema, @Table;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (SELECT 1 FROM @IgnoreTables WHERE TableName = @Table)
    BEGIN
        DECLARE curFKs3 CURSOR LOCAL FAST_FORWARD FOR
        SELECT FKName, ColName, RefTableName, RefColName, DeleteRule FROM @AllFKs WHERE TableName = @Table;
        OPEN curFKs3;
        FETCH NEXT FROM curFKs3 INTO @FK, @Col, @RefTable, @RefCol, @DelRule;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @newRule VARCHAR(20) = CASE @DelRule 
                WHEN 'NO_ACTION' THEN 'NO ACTION' WHEN 'CASCADE' THEN 'CASCADE'
                WHEN 'SET_NULL' THEN 'SET NULL' WHEN 'SET_DEFAULT' THEN 'SET DEFAULT' END;
            BEGIN TRY
                EXEC(N'
                    ALTER TABLE ['+@Schema+'].['+@Table+'] DROP CONSTRAINT '+@FK+';
                    ALTER TABLE ['+@Schema+'].['+@Table+'] ADD CONSTRAINT '+@FK+'
                        FOREIGN KEY ('+@Col+') REFERENCES '+@RefTable+' ('+@RefCol+') ON DELETE '+@newRule+';');
                PRINT '  ==> '+@FK+' restored to '+@newRule;
            END TRY
            BEGIN CATCH
                PRINT '  ==> RESTORE FAIL '+@FK+': '+ERROR_MESSAGE();
            END CATCH
            FETCH NEXT FROM curFKs3 INTO @FK, @Col, @RefTable, @RefCol, @DelRule;
        END
        CLOSE curFKs3; DEALLOCATE curFKs3;
    END
    FETCH NEXT FROM curTables3 INTO @Schema, @Table;
END
CLOSE curTables3; DEALLOCATE curTables3;
PRINT 'All done.';

關鍵細節與風險控制

  • LOCAL FAST_FORWARD 游標:唯讀、順向、低開銷。
  • 每個 DDL 包在獨立交易中,失敗不影響其他表。
  • DBCC CHECKIDENT (..., RESEED, 0):下一筆插入從 1 開始;若表為空且從未插入過,改用 RESEED, 0 後需插入一筆再刪除才生效,或直接 TRUNCATE TABLE(但 TRUNCATE 不觸發 Trigger、不記錄日誌、需無 FK 參考)。
  • 大表刪除建議分批(參考 73449 第 15 項),避免記錄檔暴增與長時間鎖定。
  • 執行前務必完整備份,並確認無業務連線。

替代方案對比

方法優點缺點適用
本腳本 (DELETE+CASCADE)保留 Trigger、權限、統計資訊;可控慢、記錄檔大、需暫改 FK中小型資料庫、需保留物件屬性
TRUNCATE TABLE (逐表)極快、最少記錄檔、重設 Identity需先移除所有 FK、不觸發 Trigger、無法 WHERE大表、無 FK 或可暫時移除 FK
卸離/刪除檔案/重建最乾淨、檔案級重置極高風險、需離線、權限複雜極端情況、開發環境
重新部署 DACPAC/BACPAC結構+資料版本化、CI/CD 友善需工具鏈、大資料庫部署慢DevOps 流程標準化
copyright © 萬盛學電腦網 all rights reserved