舊系統資料庫大量欄位預設值為 `NULL`,導致應用程式頻繁判斷 `IS NULL` 或串接字串出錯。需批次將:
-- 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` 確保唯一且可讀,移除空白避免語法錯誤。 |
-- 刪除特定約束範例
ALTER TABLE [TableName] DROP CONSTRAINT [DF_TableName_ColumnName];
可依 `@TargetCols` 記錄反向產生 `DROP CONSTRAINT` 腳本。