I've got a database that includes an
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;