SQL Server 資料清洗：使用預存程序批次修剪字串欄位空白與統一大小寫

使用者輸入資料常含前後空白、大小寫不一致等髒資料，導致比對、聯結、報表失誤。透過動態 SQL 遍歷所有使用者資料表的 varchar 欄位，批次執行 LTRIM(RTRIM()) 清理，可一次解決全庫字串欄位格式問題。

完整預存程序碼

/* 整理系統資料：移除所有 varchar 欄位前後空白 */
CREATE PROCEDURE dbo.pro_CleanupData
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tableName sysname, @columnName sysname;
    DECLARE @sql NVARCHAR(MAX);

    -- 找出所有使用者資料表的 varchar 欄位
    DECLARE cur_find CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name),
               QUOTENAME(c.name)
        FROM sys.columns c
        JOIN sys.objects o ON c.object_id = o.object_id
        JOIN sys.types t ON c.user_type_id = t.user_type_id
        WHERE o.type = 'U'                     -- 使用者資料表
          AND t.name = 'varchar'               -- 僅 varchar 型別
          AND o.name <> 'dtproperties';        -- 排除系統圖表表

    OPEN cur_find;
    FETCH NEXT FROM cur_find INTO @tableName, @columnName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 動態建構 UPDATE 陳述式
        SET @sql = N'UPDATE ' + @tableName + 
                   N' SET ' + @columnName + N' = LTRIM(RTRIM(' + @columnName + N'))' +
                   N' WHERE ' + @columnName + N' IS NOT NULL' +
                   N'   AND ' + @columnName + N' <> LTRIM(RTRIM(' + @columnName + N'))'; -- 僅更新真正有空白的列，減少日誌

        -- 執行
        EXEC sp_executesql @sql;

        FETCH NEXT FROM cur_find INTO @tableName, @columnName;
    END

    CLOSE cur_find;
    DEALLOCATE cur_find;
END;
GO

關鍵改進說明

• 安全識別碼：使用 QUOTENAME() 防止物件名稱含特殊字元/保留字導致注入或語法錯誤。
• Schema 完整名稱：SCHEMA_NAME(o.schema_id) 避免同名表不同 Schema 衝突。
• WHERE 條件過濾：僅更新真正有前後空白的列，大幅減少交易日誌產生、鎖持有時間與觸發器觸發次數。
• 游標選項：LOCAL FAST_FORWARD READ_ONLY 為唯讀順向游標，效能最佳、資源佔用最低。
• 系統目錄檢視：採用 sys.columns/sys.objects/sys.types 取代舊版 syscolumns/sysobjects，相容新版本。

擴展：統一大小寫

若需統一為大寫或小寫，將 UPDATE 句修改為：

-- 統一大寫
SET @sql = N'UPDATE ' + @tableName + 
           N' SET ' + @columnName + N' = UPPER(LTRIM(RTRIM(' + @columnName + N')))' +
           N' WHERE ' + @columnName + N' IS NOT NULL' +
           N'   AND ' + @columnName + N' <> UPPER(LTRIM(RTRIM(' + @columnName + N')))';

-- 統一小寫
SET @sql = N'UPDATE ' + @tableName + 
           N' SET ' + @columnName + N' = LOWER(LTRIM(RTRIM(' + @columnName + N')))' +
           N' WHERE ' + @columnName + N' IS NOT NULL' +
           N'   AND ' + @columnName + N' <> LOWER(LTRIM(RTRIM(' + @columnName + N')))';

執行建議

1. 先在測試環境執行並驗證資料正確性。
2. 生產環境建議離峰時段分批次執行（可加入 TOP (10000) 分頁更新），避免長時間鎖定大表。
3. 執行前完整備份資料庫，或使用資料庫快照（Enterprise 版）作為回滾點。
4. 執行後執行 UPDATE STATISTICS 更新統計資料，確保查詢最佳化器有正確基數。

現代替代方案

• 計算欄位 + 索引：ADD CleanName AS LTRIM(RTRIM(RawName)) PERSISTED，查詢直接用乾淨欄位，寫入時自動維護。
• CHECK CONSTRAINT：ADD CONSTRAINT CHK_Name_NoSpace CHECK (RawName = LTRIM(RTRIM(RawName)))，從來源攔截髒資料。
• ETL/SSIS 資料流程：在載入階段以 Derived Column Transformation 清洗，落地即乾淨。