Set default value for all columns with specific name within a database

Question

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;

Are you sure all of the columns have the same name (including CaSe SenSiTive), and are the same data type? — Aaron Bertrand
– Aaron Bertrand, Commented Aug 6, 2014 at 14:35

Aaron Bertrand · Accepted Answer · 2018-01-23 15:53:02Z

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

Michael Green · Accepted Answer · 2014-08-06 11:27:27Z

0

By running this:

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'project_id'

you will get a list of all the tables that contain columns of that name. From there you can construct SQL using much the same query:

select 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME ..... etc.

to construct the required ALTER statements. Harvest the output of this, paste it to SSMS and viola! Job done.

answered Aug 6, 2014 at 11:27

Michael Green

25.3k13 gold badges54 silver badges100 bronze badges

Keep in mind that the column already has a default value set - if I'm not mistaken, I'm going to want to drop that constraint first before adding the new one, so using your method I also need a way of determining the constraint name for the default value

Cocowalla
– Cocowalla

2014-08-06 11:40:56 +00:00
Commented Aug 6, 2014 at 11:40
You should use sys.default_constraints to get the default constraint names that you need to drop and then add anew with the proper setting.

RLF
– RLF

2014-08-06 13:26:07 +00:00
Commented Aug 6, 2014 at 13:26

Add a comment |

Stack Exchange Network

Set default value for all columns with specific name within a database

2 Answers 2

Your Answer

Linked

Hot Network Questions

Set default value for all columns with specific name within a database

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions