SQL Server 批次修改欄位預設值：動態 SQL 掃描全庫表欄位、排除身分識別/已有預設、依型別設定 0 或空字串

前言

大型資料庫中，大量欄位預設值為 NULL，導致應用層頻繁判斷 IS NULL、串接字串時需 COALESCE。需求：將所有 INT 欄位預設改為 0，所有 VARCHAR 欄位預設改為空字串 ''，且排除身分識別欄位、已有預設值的欄位。本文提供一支動態 SQL 儲存程序完整實作。

完整程式碼

-- 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                        -- 排除身分識別欄位 (0x80 = IDENTITY)
  AND NOT EXISTS (                            -- 排除已有 DEFAULT 條件約束的欄位
    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'                   -- D = DEFAULT constraint
          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, @tbname VARCHAR(256), @colname VARCHAR(256), @xtype VARCHAR(20), @sql NVARCHAR(4000);

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

    -- 動態組裝 ALTER TABLE ... ADD CONSTRAINT ... DEFAULT ...
    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 sp_executesql @sql;

    SET @i = @i + 1;
END

關鍵邏輯解析

擴充建議：支援更多型別

-- 型別對應表 (可改為查表驅動)
CASE c.name
    WHEN 'int'        THEN '0'
    WHEN 'bigint'     THEN '0'
    WHEN 'smallint'   THEN '0'
    WHEN 'tinyint'    THEN '0'
    WHEN 'bit'        THEN '0'
    WHEN 'decimal'    THEN '0.0'
    WHEN 'numeric'    THEN '0.0'
    WHEN 'money'      THEN '0.00'
    WHEN 'float'      THEN '0.0'
    WHEN 'real'       THEN '0.0'
    WHEN 'datetime'   THEN 'GETDATE()'  -- 或 '1900-01-01'
    WHEN 'smalldatetime' THEN 'GETDATE()'
    WHEN 'date'       THEN 'GETDATE()'
    WHEN 'time'       THEN '00:00:00'
    WHEN 'varchar'    THEN ''''''
    WHEN 'nvarchar'   THEN N''''''
    WHEN 'char'       THEN ''''''
    WHEN 'nchar'      THEN N''''''
    WHEN 'text'       THEN ''''''
    WHEN 'ntext'      THEN N''''''
    ELSE NULL
END

執行前檢核清單

1. 備份資料庫：結構變更不可逆，必須有完整備份。
2. 測試環境先行：在非生產環境完整跑一次，驗證約束名稱無衝突、應用程式行為正常。
3. 應用程式影響評估：原本依賴 NULL 語意 (如「未填寫」vs「填寫空值」) 的邏輯需同步調整。
4. 大量表分批執行：上萬張表時，建議分批 (每批 500~1000) 並監控鎖定/記錄檔增長。
5. 權限：需 ALTER 表權限 (通常 db_ddladmin 或 db_owner)。

結語

透過系統目錄檢視 (sysobjects/syscolumns/systypes) 動態掃描、精準排除身分識別與既有預設、依型別賦予合理預設值，這支腳本能一次性解決百萬欄位的預設值標準化問題。關鍵在於約束命名唯一性、動態 SQL 安全組裝、完整備份與測試先行。納入資料庫標準化治理流程，定期掃描新增表欄位自動套用，可維持長期一致性。