為數據庫中具有特定名稱的所有列設置預設值

我有一個數據庫，其中包含許多表上的int列project_id，並且我想編寫腳本將預設值設置為數據庫中所有具有該名稱的列的特定值。請注意，該列已經設置了預設值，我想更改它。

我知道如何為單個表執行此操作，但不確定如何為包含該列的所有表編寫腳本project_id而不手動指定表的名稱。

**** 更新 ****

根據亞倫的回答，這就是我最終想出的：

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @new_id INT = 9;

SELECT 
   @sql += Query
FROM
 (
   SELECT 
     'IF (EXISTS(SELECT 1 FROM sys.default_constraints WHERE name = ''DF_' + OBJECT_NAME(parent_object_id) + '_project_id'' AND type = ''D'')) BEGIN ALTER TABLE [' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + '] END;' + CHAR(13) + CHAR(10) AS Query
   FROM 
     sys.default_constraints
   WHERE
     name = 'DF_' + OBJECT_NAME(parent_object_id) + '_project_id'

   UNION ALL

   SELECT
      'ALTER TABLE [' + OBJECT_NAME(object_id) + '] ' +
      'ADD CONSTRAINT [DF_' + OBJECT_NAME(object_id) + '_project_id] ' +
      'DEFAULT ((' + CAST(@new_id AS NVARCHAR) + ')) ' +
      'FOR [project_id];' + CHAR(13) + CHAR(10) AS Query
   FROM 
     sys.columns
   WHERE
     name = 'project_id' AND
     system_type_id = 56
 ) AS Queries

PRINT @sql
EXEC sp_executesql @sql;

假設您想要的新預設值是5：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + ';
 ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c 
 + ' DEFAULT (5) FOR [project_id];'
FROM
(
 SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name), 
   c = QUOTENAME(d.name)
 FROM sys.tables AS t
 INNER JOIN sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 INNER JOIN sys.default_constraints AS d
 ON d.parent_object_id = t.[object_id]
 AND d.parent_column_id = c.column_id
 WHERE c.name = N'project_id'
) AS x;

PRINT @sql;
--EXEC sp_executesql @sql;

筆記：

1. print限制為 8K，因此如果您有很多包含此列的表，您可能實際上看不到 SSMS 中的整個命令輸出，並且它可能看起來被截斷。
2. 這只會將約束添加到已經**具有預設約束的project_id列。如果您有其他具有此列但目前沒有約束的表，則需要單獨的查詢。
3. 這不會更改基於舊預設值已生成的值。

這就是為什麼我建議不要使用 INFORMATION_SCHEMA 來解決此類問題：

• https://sqlblog.org/2011/11/03/the-case-against-information_schema-views

引用自：https://dba.stackexchange.com/questions/73277