SQL Server 批次修改欄位預設值：動態 T-SQL 腳本自動化處理 INT/VARCHAR 欄位

資料庫繼承早期設計，大量欄位預設值為 NULL，導致應用層頻繁判斷 IS NULL。本文提供動態 T-SQL 腳本，掃描所有使用者資料表的 INT、VARCHAR 欄位，自動為無預設值、非身分識別欄位加上預設值（INT→0、VARCHAR→''），並附帶完整註解與邏輯說明。

完整執行腳本

-- 1. 收集目標欄位資訊到暫存表變數
DECLARE @t TABLE (
  id INT IDENTITY(1,1),
  tbname VARCHAR(256),
  colname VARCHAR(256),
  xtype VARCHAR(20)
);

INSERT INTO @t
SELECT a.name, b.name, c.name
FROM sysobjects a
INNER JOIN syscolumns b ON a.id = b.id
INNER JOIN systypes c ON b.xusertype = c.xusertype
WHERE a.xtype = 'u'                    -- 使用者資料表
  AND c.name IN ('varchar', 'int')     -- 僅處理這兩類型
  AND b.status <> 0x80                 -- 排除身分識別欄位 (IDENTITY)
  AND NOT EXISTS (                     -- 排除已有預設值的欄位
    SELECT 1
    FROM (
      SELECT (SELECT name FROM sysobjects WHERE id = c.id) AS 表名,
             (SELECT name FROM syscolumns WHERE cdefault = a.id) AS 欄位名
      FROM sysobjects b, syscolumns c, syscomments a
      WHERE b.xtype = 'D'              -- DEFAULT 約束
        AND a.id = b.id
        AND b.parent_obj = c.id
        AND a.colid = c.colid
    ) t
    WHERE a.name = t.表名 AND b.name = t.欄位名
  );

-- 2. 迴圈逐一建立 DEFAULT 約束
DECLARE @i INT = 1, @max_id INT = (SELECT MAX(id) FROM @t);
DECLARE @tbname VARCHAR(256), @colname VARCHAR(256), @xtype VARCHAR(20), @sql NVARCHAR(4000);

WHILE @i <= @max_id
BEGIN
  SELECT @tbname = tbname, @colname = colname, @xtype = xtype FROM @t WHERE id = @i;

  SET @sql = 'ALTER TABLE [' + @tbname + '] ADD CONSTRAINT ' + 
             'df_' + REPLACE(@tbname, '-', '') + '_' + REPLACE(@colname, '-', '') + ' DEFAULT ';

  IF @xtype = 'int'
    SET @sql = @sql + '0';
  ELSE IF @xtype = 'varchar'
    SET @sql = @sql + '''''';   -- 空字串

  SET @sql = @sql + ' FOR [' + @colname + ']';
  
  PRINT @sql;   -- 可先列印檢查，確認無誤再執行 EXEC(@sql)
  -- EXEC(@sql);

  SET @i = @i + 1;
END

邏輯關鍵點解析

執行前檢核

• [ ] 先在測試環境執行 PRINT @sql 檢視產生的 DDL。
• [ ] 確認約束命名不衝突（同表同欄位只能有一個 DEFAULT）。
• [ ] 評估資料表大小：大表加預設值為 **中繼資料操作**，近乎瞬間完成，不鎖定資料列。
• [ ] 備份資料庫或建立快照，以便回滾。

擴充建議

• 支援更多型別：DATETIME → GETDATE()、BIT → 0、DECIMAL → 0.00。
• 加入 SCHEMA 支援（sysobjects.uid 關聯 sysusers）。
• 輸出執行日誌至永久表，供稽核追蹤。

善用動態 SQL 批次處理結構變更，能將數天人工作業壓縮為幾分鐘自動化執行，並大幅降低人為疏漏風險。