SQL Server 批量更新所有表中 datetime 欄位：游標動態構建執行方案

業務場景

歷史數據遷移、測試數據生產或時區統一調整時，常需將庫中所有表的 datetime 類型欄位整體偏移（如加 10 年）。手寫每張表的 UPDATE 不現實，需動態遍歷元數據生成執行腳本。

核心腳本解析

DECLARE @tablename NVARCHAR(80), @column NVARCHAR(80), @sql NVARCHAR(400);

DECLARE c1 CURSOR SCROLL FOR
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'datetime';

OPEN c1;
FETCH FIRST FROM c1 INTO @tablename, @column;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'UPDATE ' + @tablename + 
               ' SET ' + @column + ' = DATEADD(yy, 10, ' + @column + ')' +
               ' WHERE ' + @column + ' IS NOT NULL';
    EXEC (@sql);
    FETCH NEXT FROM c1 INTO @tablename, @column;
END

CLOSE c1;
DEALLOCATE c1;

關鍵點詳解

1. 元數據查詢：INFORMATION_SCHEMA.COLUMNS 是 ANSI 標準視圖，跨版本兼容性好。
2. SCROLL 游標：支持 FETCH FIRST/NEXT，代替傳統 FETCH NEXT 循環，邏輯更清晰。
3. 動態 SQL：利用 EXEC (@sql) 執行拼接字符串，實現表名、列名參數化。
4. DATEADD 函數：yy 代表年，10 為增量，可根據需求改為 mm、dd、hh 等。
5. 非空過濾：WHERE column IS NOT NULL 避免對 NULL 值操作產生邏輯錯誤或性能浪費。

生產環境優化建議

• 分批提交：大表更新易導致日誌暴漲、鎖升級。建議在循環中加入 SET ROWCOUNT 5000 分批處理，或使用 TOP (5000) 子句。
• 事務控制：單表更新隱式事務，跨表無統一事務。若需原子性，需外層包裹顯式事務，但會長時間持有鎖，權衡業務容忍度。
• 索引影響：更新索引欄位會導致頁分裂，更新前考慮禁用非聚簇索引，更新後重建。
• 審計與回滾：執行前務必全量備份；生產庫建議先在備份恢復環境演練。

擴展：通用化存儲過程

可將上述邏輯封裝為存儲過程 sp_BatchUpdateDateTime @YearOffset INT，參數化偏移量，便於調度任務調用。