SQL Server 游標批量清理多表髒數據：動態 SQL + 系統表遍歷實戰

當需要按相同邏輯清理數十上百張表的髒數據（如軟刪除標記、孤兒記錄、測試殘留）時，游標配合動態 SQL 是可行方案。本文展示兩種實戰模式：全表清空與條件式清理。

模式一：遍歷所有用戶表執行 TRUNCATE/DELETE

CREATE PROCEDURE dbo.CleanAllTables
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName SYSNAME;
    DECLARE @Sql NVARCHAR(MAX);

    -- 僅遍歷基礎表，排除系統表
    DECLARE Cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND TABLE_NAME <> 'dtproperties';  -- 舊版兼容

    OPEN Cur;
    FETCH NEXT FROM Cur INTO @TableName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 方案 A：TRUNCATE（需無外鍵、權限足夠，極快、不記日志）
        -- SET @Sql = N'TRUNCATE TABLE ' + @TableName;

        -- 方案 B：DELETE（有外鍵/觸發器時必須用，記錄日志）
        SET @Sql = N'DELETE FROM ' + @TableName;

        EXEC sp_executesql @Sql;

        FETCH NEXT FROM Cur INTO @TableName;
    END
    CLOSE Cur;
    DEALLOCATE Cur;
END;
GO

模式二：針對特定業務表的條件式清理（實戰案例）

場景：專案範圍樹、段落設置、測點表三層關聯，需清理：

• ProjectRangeTree：DeleteVersion>0、孤兒節點（父級不存在）
• Section_Settings：引用不存在的樹節點
• 各測點表（名稱存儲在 Section_Type）：引用不存在的測點設置

步驟 1：靜態清理固定表

-- 軟刪除標記
DELETE FROM ProjectRangeTree WHERE DeleteVersion > 0;

-- 孤兒節點（遞歸層級 2~5）
DELETE FROM ProjectRangeTree WHERE Type = 3 AND ParentId NOT IN (SELECT Id FROM ProjectRangeTree WHERE Type = 2);
DELETE FROM ProjectRangeTree WHERE Type = 4 AND ParentId NOT IN (SELECT Id FROM ProjectRangeTree WHERE Type = 3);
DELETE FROM ProjectRangeTree WHERE Type = 5 AND ParentId NOT IN (SELECT Id FROM ProjectRangeTree WHERE Type = 4);

-- 區段設置孤兒
DELETE FROM Section_Settings WHERE Parent_Prj_Tree_Id NOT IN (SELECT Id FROM ProjectRangeTree);

步驟 2：動態遍歷測點表清理孤兒測點

DECLARE @TableName SYSNAME;
DECLARE @Sql NVARCHAR(MAX);

DECLARE Cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT QUOTENAME(Table_Name) FROM dbo.Section_Type;

OPEN Cur;
FETCH NEXT FROM Cur INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 刪除測點表中不存在於 Measuring_Point_Setting 的記錄
    SET @Sql = N'DELETE FROM ' + @TableName + 
               N' WHERE Measuring_Point_Id NOT IN (SELECT Id FROM Measuring_Point_Setting)';
    
    EXEC sp_executesql @Sql;
    
    FETCH NEXT FROM Cur INTO @TableName;
END
CLOSE Cur;
DEALLOCATE Cur;

關鍵技術點解析

效能與安全建議

1. 大表分批刪除：DELETE TOP (5000) ... 循環，避免長事務鎖表、日志暴增。
2. 外鍵約束：若表有外鍵，TRUNCATE 會失敗，必須用 DELETE 或先禁用約束。
3. 事務控制：每表獨立提交（隱式自動提交），或在外層包裹 BEGIN TRAN ... COMMIT 視業務一致性要求而定。
4. 審計日志：清理前 INSERT INTO AuditLog SELECT * FROM TargetTable WHERE ... 留存證據。
5. 測試優先：在備份庫/快照庫全流程跑通，核對行數再上生產。

替代方案：集合化動態 SQL（無游標）

DECLARE @Sql NVARCHAR(MAX) = N'';

SELECT @Sql += N'DELETE FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + 
               N' WHERE Measuring_Point_Id NOT IN (SELECT Id FROM Measuring_Point_Setting);'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME IN (SELECT Table_Name FROM dbo.Section_Type);

EXEC sp_executesql @Sql;

優勢：單批次提交、編譯一次、無游標開銷。限制：@Sql 長度受限 2GB，表數極多時仍需分批。