批次修改 SQL Server 預設值約束：查詢、刪除、重建完整腳本

需求：大量變更資料庫中所有資料表的 DEFAULT 約束 (如統一改為 1)。本文提供三步驟動態 SQL 腳本：查詢現有約束 → 產生刪除腳本 → 產生重建腳本，可直接複製執行或進一步參數化。

1. 查詢所有資料表的 DEFAULT 約束

SELECT
    t3.name AS 表名,
    t1.name AS 欄位名,
    t2.text AS 預設值,
    t4.name AS 約束名
FROM syscolumns t1
JOIN syscomments t2 ON t1.cdefault = t2.id
JOIN sysobjects t3 ON t3.id = t1.id AND t3.xtype = 'U'
JOIN sysobjects t4 ON t4.id = t2.id AND t4.xtype = 'D';

說明：syscolumns、syscomments、sysobjects 為 SQL Server 2000 相容檢視，2005+ 建議改用 sys.default_constraints、sys.columns、sys.tables。

2. 產生刪除所有 DEFAULT 約束的 ALTER TABLE 腳本

SELECT 'ALTER TABLE ' + t3.name + ' DROP CONSTRAINT ' + t4.name + ';'
FROM syscolumns t1
JOIN syscomments t2 ON t1.cdefault = t2.id
JOIN sysobjects t3 ON t3.id = t1.id AND t3.xtype = 'U'
JOIN sysobjects t4 ON t4.id = t2.id AND t4.xtype = 'D';

將結果複製到新視窗執行即可批次移除。

3. 產生重建 DEFAULT 約束腳本 (統一設為 1)

SELECT 'ALTER TABLE ' + t3.name + ' ADD DEFAULT (1) FOR ' + t1.name + ';'
FROM syscolumns t1
JOIN syscomments t2 ON t1.cdefault = t2.id
JOIN sysobjects t3 ON t3.id = t1.id AND t3.xtype = 'U'
JOIN sysobjects t4 ON t4.id = t2.id AND t4.xtype = 'D';

現代化寫法 (SQL Server 2005+ 推薦)

-- 查詢
SELECT
    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    c.name AS ColumnName,
    dc.name AS ConstraintName,
    dc.definition AS DefaultValue
FROM sys.default_constraints dc
JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id;

-- 產生 DROP
SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
     + ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.default_constraints;

-- 產生 ADD (指定新預設值)
SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
     + ' ADD CONSTRAINT ' + QUOTENAME('DF_' + OBJECT_NAME(parent_object_id) + '_' + c.name)
     + ' DEFAULT (1) FOR ' + QUOTENAME(c.name) + ';'
FROM sys.default_constraints dc
JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id;

執行建議

1. 先在測試環境驗證產生的腳本正確性。
2. 生產環境執行前務必完整備份資料庫。
3. 若資料表極多，建議分批次執行，避免長時間鎖定系統表。
4. 使用 QUOTENAME 防止識別碼含特殊字元導致語法錯誤。

善用目錄檢視動態產生 DDL，讓大量結構變更變得可控、可追蹤、可重複執行。