Set default value for all columns with specific name within a database I've got a database that includes an int column project_id on many tables, and I want to script setting the default value to a specific value for all columns with that name in the database. Note that the column will already have a default value set, and I want to change it.

I know how to do this for individual tables, but am unsure how to script doing it for all tables containing the column project_id without manually specifying the names of the tables.

** UPDATE **

Based on Aaron's answer, this is what I finally came up with:

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;
Let's say the new default you want is 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;


Notes:


print is limited to 8K, so if you have a lot of tables with this column, you might not actually see the entire command output in SSMS, and it may look truncated.
This will only add the constraint to project_id columns that already have a default constraint. If you have other tables that have this column but don't currently have a constraint, you'll need a separate query.
This will not change the values already generated based on the old defaults.


Here is why I recommend against INFORMATION_SCHEMA for this type of problem:


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