Thursday, March 19, 2015

Change all column default values with a certain value to another value in T-SQL

Just to remember this for future work. I wanted to replace GetDate() default column values with SysUtcDatetime(). This is the script used:
-- declare a string that will hold the actual SQL executed
DECLARE @SQL NVARCHAR(Max) = ''
SELECT @SQL=@SQL+
N'ALTER TABLE ['+t.name+'] DROP CONSTRAINT ['+o.name+'];
ALTER TABLE ['+t.name+'] ADD DEFAULT SYSUTCDATETIME() FOR ['+c.name+'];
'  -- drop the default value constraint, then add another with SYSUTCDATETIME() as default value
 FROM sys.all_columns c  -- get the name of the columns
INNER JOIN sys.tables t  -- get the name of the tables containing the columns
ON c.object_id=t.object_id
INNER JOIN sys.default_constraints o -- we are only interested in default value constraints
ON c.default_object_id=o.object_id
WHERE o.definition='(getdate())' -- only interested in the columns with getdate() as default value

-- execute generated SQL
EXEC sp_executesql @SQL

0 comments: