萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server 批次修改資料表欄位預設值腳本 (varchar→''、int→0)

應用場景

舊系統資料庫大量欄位預設值為 `NULL`,導致應用程式頻繁判斷 `IS NULL` 或串接字串出錯。需批次將:

  • `varchar`/`nvarchar`/`char`/`nchar` 類型欄位預設值設為空字串 `''`。
  • `int`/`bigint`/`smallint`/`tinyint` 類型欄位預設值設為 `0`。
  • 跳過:身分識別欄位 (`IDENTITY`)、已有預設約束的欄位。

完整執行腳本

-- 1. 收集目標欄位至暫存表
DECLARE @TargetCols TABLE (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TableName SYSNAME,
    ColumnName SYSNAME,
    DataType SYSNAME
);

INSERT INTO @TargetCols (TableName, ColumnName, DataType)
SELECT 
    o.name AS TableName,
    c.name AS ColumnName,
    t.name AS DataType
FROM sysobjects o
INNER JOIN syscolumns c ON o.id = c.id
INNER JOIN systypes t ON c.xusertype = t.xusertype
WHERE o.xtype = 'U'                     -- 使用者資料表
  AND t.name IN ('varchar','nvarchar','char','nchar','int','bigint','smallint','tinyint')
  AND (c.status & 0x80) = 0             -- 排除 IDENTITY 欄位 (status bit 7)
  AND NOT EXISTS (                      -- 排除已有 DEFAULT 約束
      SELECT 1 FROM sysobjects dobj
      INNER JOIN syscolumns dcol ON dobj.parent_obj = dcol.id
      INNER JOIN syscomments dcom ON dobj.id = dcom.id
      WHERE dobj.xtype = 'D'            -- DEFAULT 約束
        AND dcol.colid = c.colid
        AND dcol.id = c.id
        AND dcom.colid = c.colid
  );

-- 2. 迴圈產生並執行 ALTER TABLE ADD CONSTRAINT
DECLARE @i INT = 1, @max INT, @tbl SYSNAME, @col SYSNAME, @typ SYSNAME, @sql NVARCHAR(MAX);
SELECT @max = MAX(ID) FROM @TargetCols;

WHILE @i <= @max
BEGIN
    SELECT @tbl = TableName, @col = ColumnName, @typ = DataType FROM @TargetCols WHERE ID = @i;
    
    SET @sql = 'ALTER TABLE [' + @tbl + '] ADD CONSTRAINT [DF_' 
               + REPLACE(@tbl, ' ', '_') + '_' + REPLACE(@col, ' ', '_') + '] DEFAULT ';
    
    IF @typ IN ('int','bigint','smallint','tinyint')
        SET @sql += '0 ';
    ELSE
        SET @sql += ''''' ';  -- 空字串
    
    SET @sql += 'FOR [' + @col + '];';
    
    -- PRINT @sql; -- 偵錯輸出
    EXEC sp_executesql @sql;
    
    SET @i += 1;
END

PRINT '批次修改預設值完成,共處理 ' + CAST(@max AS VARCHAR(10)) + ' 個欄位。';

關鍵技術點解析

技術點說明
syscolumns.status & 0x80位元遮罩檢查 IDENTITY 屬性 (bit 7 = 128)。
sysobjects.xtype = 'D'識別 DEFAULT 約束物件類型。
動態 SQL + sp_executesql避免 SQL 注入風險,支援參數化 (此處單純拼接 DDL)。
約束命名規則`DF_TableName_ColumnName` 確保唯一且可讀,移除空白避免語法錯誤。

執行前檢查清單

  1. 完整資料庫備份 (含結構)。
  2. 離線/維護時段執行,避免鎖定衝突。
  3. 先在測試環境驗證腳本邏輯與影響範圍。
  4. 確認應用程式可正常處理新預設值 (如 `''` vs `NULL` 的語意差異)。

回滾方案

-- 刪除特定約束範例
ALTER TABLE [TableName] DROP CONSTRAINT [DF_TableName_ColumnName];

可依 `@TargetCols` 記錄反向產生 `DROP CONSTRAINT` 腳本。

copyright © 萬盛學電腦網 all rights reserved