前言

在管理資料庫時，我們經常會遇到需要在多個資料表中更新相同欄位的情況。例如：修改某個代碼、更新狀態值等。如果要手動找出所有包含該欄位的資料表並逐一更新，不只費時還容易出錯。今天就來分享一個實用的 SQL 技巧，教你如何快速找出並更新特定欄位！

使用情境

假設我們需要將所有資料表中的 MaintainClassID 欄位從 4851204 更新為 4851206。在大型資料庫中，這個欄位可能分散在多個資料表中。如何一次找出所有包含此欄位的資料表呢？

解決方案

我們可以利用 SQL Server 的 INFORMATION_SCHEMA 視圖來查詢資料庫結構，並產生所需的更新語句。讓我們一步步來看：

步驟一：找出所有包含特定欄位的資料表

-- 找到所有有該欄位的資料表
SELECT DISTINCT
    t.TABLE_SCHEMA, -- 資料表的架構名稱
    t.TABLE_NAME   -- 資料表名稱
FROM
    INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN
    INFORMATION_SCHEMA.TABLES t 
        ON c.TABLE_NAME = t.TABLE_NAME 
        AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE
    c.COLUMN_NAME = 'MaintainClassID' -- 要查找的欄位名稱
    AND t.TABLE_TYPE = 'BASE TABLE';  -- 確保僅查找資料表，不包含視圖

程式碼解析：

INFORMATION_SCHEMA.COLUMNS：此視圖包含所有欄位的資訊
INFORMATION_SCHEMA.TABLES：此視圖包含所有資料表的資訊
INNER JOIN：合併兩個視圖的資料，確保欄位和資料表資訊相符
WHERE 條件：
- 指定要查找的欄位名稱
- TABLE_TYPE = 'BASE TABLE' 過濾掉視圖，只保留實體資料表

步驟二：產生更新語句

-- 產生更新語句
SELECT
    'UPDATE [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + 
    '] SET MaintainClassID = 4851206 WHERE MaintainClassID = 4851204;'
    AS UpdateStatement
FROM
    INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN
    INFORMATION_SCHEMA.TABLES t 
        ON c.TABLE_NAME = t.TABLE_NAME 
        AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE
    c.COLUMN_NAME = 'MaintainClassID'
    AND t.TABLE_TYPE = 'BASE TABLE'
    AND t.TABLE_NAME IN (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = 'MaintainClassID'
    );

程式碼解析：

使用字串串接（+）來組合完整的 UPDATE 語句
加入方括號 [] 來處理可能包含特殊字元的架構名稱和資料表名稱
子查詢確保只更新有指定欄位的資料表

使用注意事項

執行更新前請先備份資料庫
建議先用 SELECT 語句測試確認影響的資料範圍
大量資料更新時要注意交易記錄檔的大小
考慮在非尖峰時段執行更新作業

實用擴展

這個技巧不只適用於更新，還可以用於：

查找重複欄位名稱
檢查欄位定義一致性
產生資料庫文件
自動化資料庫維護工作

總結

透過 INFORMATION_SCHEMA 視圖，我們可以輕鬆地管理和維護資料庫。這個技巧不僅節省時間，還能降低人為錯誤的風險。下次遇到需要大量更新資料的情況，就可以運用這個方法了！

SQL Server管理技巧：批量更新資料的神奇小法術

前言

使用情境

解決方案

步驟一：找出所有包含特定欄位的資料表

程式碼解析：

步驟二：產生更新語句

程式碼解析：

使用注意事項

實用擴展

總結

參考資料