0

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 Aug 6 '14 at 14:35
  • @AaronBertrand yes – Cocowalla Aug 6 '14 at 14:39
2

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:

  1. 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.
  2. 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.
  3. 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:

| improve this answer | |
  • Great answer that let's me do what I want in one go, thanks! – Cocowalla Aug 6 '14 at 15:38
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.

| improve this answer | |
  • 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 Aug 6 '14 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 Aug 6 '14 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.