問題情境

資料庫裡有個代碼欄位 MaintainClassID，散落在幾十張資料表中。要把值 4851204 全部改成 4851206，逐張查、逐張改——這不只費時，還容易漏掉某張表。

有沒有辦法讓 SQL Server 自己列出所有含這個欄位的資料表，再批量產生 UPDATE 語句？有。

解決方案

INFORMATION_SCHEMA.COLUMNS 視圖記錄了目前資料庫所有欄位的中繼資料，查一次就能得到所有包含目標欄位的資料表清單。

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

-- 找到所有有該欄位的資料表
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' 排除視圖，只保留實體資料表

步驟二：產生 UPDATE 語句

-- 產生批量更新語句
SELECT DISTINCT
    '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';

語句解析

用字串串接（+）組合完整的 UPDATE 語句
欄位名和資料表名加方括號 []，避免名稱含保留字或特殊字元時出錯
JOIN 條件確保只針對有該欄位的資料表產生語句

步驟三：執行產生的語句

步驟二的 SELECT 輸出是一組 UPDATE 字串，有兩種方式執行：

方式一：複製貼上（適合少量資料表，執行前可人工確認）

在 SSMS 結果窗格全選複製，貼到新查詢視窗執行。執行前建議先看一遍清單，確認沒有誤判的資料表。

方式二：動態 SQL（適合資料表多、想一次自動跑完）

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

SELECT @sql += 
    'UPDATE [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME +
    '] SET [MaintainClassID] = 4851206 WHERE [MaintainClassID] = 4851204;' + CHAR(13)
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';

-- 先印出來確認再決定要不要執行
PRINT @sql;

-- 確認無誤後再取消註解執行
-- EXEC sp_executesql @sql;

這段先把所有 UPDATE 語句串成一個字串並印出，確認無誤後再取消 EXEC 那行的註解執行。

使用注意事項

執行更新前先備份資料庫，或至少確認目前有完整備份
建議先跑步驟一確認影響的資料表清單，再決定是否執行更新
大量更新會產生交易記錄，如果資料庫是 FULL 復原模式且交易記錄檔沒有定期備份，可能撐爆磁碟。執行前用 DBCC SQLPERF(LOGSPACE) 看剩餘空間；若空間緊張，考慮分批執行或暫時切換到 BULK_LOGGED 復原模式
建議在非尖峰時段執行，避免長時間鎖定資料表影響線上作業

INFORMATION_SCHEMA 的限制

INFORMATION_SCHEMA 是 ANSI 標準相容層，對跨資料庫引擎的查詢有一定優勢，但在純 SQL Server 環境，sys.columns 和 sys.tables 是更完整的選擇：

INFORMATION_SCHEMA 不包含 IDENTITY 屬性、計算欄位、稀疏欄位等 SQL Server 特有屬性
某些系統物件在 INFORMATION_SCHEMA 不可見，但在 sys.columns 看得到
Microsoft 官方建議系統管理腳本優先使用 sys 目錄檢視

本文範例用 INFORMATION_SCHEMA 是因為語法直觀且夠用。若要查詢 IDENTITY 欄位、欄位型別細節等進階資訊，改用 sys.columns 加上 sys.tables 才能取得完整結果：

-- 用 sys 目錄檢視查找特定欄位（更完整）
SELECT DISTINCT
    s.name AS TABLE_SCHEMA,
    t.name AS TABLE_NAME
FROM
    sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
    c.name = 'MaintainClassID';

實用擴展

同樣的查詢骨架可以用在其他常見場景：

查找在多張資料表中重複出現的欄位名稱

-- 找出出現在兩張（含）以上資料表的欄位名稱
SELECT
    COLUMN_NAME,
    COUNT(DISTINCT TABLE_NAME) AS TableCount
FROM
    INFORMATION_SCHEMA.COLUMNS
GROUP BY
    COLUMN_NAME
HAVING
    COUNT(DISTINCT TABLE_NAME) >= 2
ORDER BY
    TableCount DESC;

檢查同名欄位在不同資料表中的型別是否一致

-- 找出同名欄位但資料型別不同的情況
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    COUNT(*) AS OccurrenceCount,
    STRING_AGG(TABLE_SCHEMA + '.' + TABLE_NAME, ', ') AS Tables
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME = 'MaintainClassID'
GROUP BY
    COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
ORDER BY
    COLUMN_NAME;

產生欄位存在某張資料表的 ALTER TABLE 語句（新增遺漏欄位）

-- 找出哪些資料表缺少 MaintainClassID 欄位（假設應該要有）
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    'ALTER TABLE [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME +
    '] ADD [MaintainClassID] INT NULL;' AS AlterStatement
FROM
    INFORMATION_SCHEMA.TABLES t
WHERE
    t.TABLE_TYPE = 'BASE TABLE'
    AND NOT EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_NAME = t.TABLE_NAME
          AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
          AND c.COLUMN_NAME = 'MaintainClassID'
    );