Bulk renaming auto-named default constraints

When you create a default constraint on a column you do not have to specify a name for it. In this case SQL Server will assign each generate an ugly name for you like "DF__ ExampleTa __BitF__42D9640B". This can become a real problem if you have multiple instances of your database as the name of the constraint can be different in each database.

This script renames anything named automatically by sql server eg “DF__ExampleTa__BitF__42D9640B” to something that makes sense and will be the same across all databases where you run the script i.e. “DF_ExampleTable_BitFlag”.


DECLARE @tname nvarchar(MAX)
DECLARE @cname nvarchar(MAX)
DECLARE @dname nvarchar(MAX)
DECLARE @ddef nvarchar(MAX)

WHILE EXISTS (SELECT * from sys.default_constraints d WHERE d.name LIKE '%[_][_]%')

      select top 1 @tname=t.name, @cname=c.name, @dname=d.name, @ddef=d.definition
      from sys.tables t
            sys.default_constraints d
                  on d.parent_object_id = t.object_id
            sys.columns c
                  on c.object_id = t.object_id
                  and c.column_id = d.parent_column_id
      WHERE d.name LIKE '%[_][_]%'
      PRINT 'alter table [dbo].['+@tname+'] drop constraint ['+@dname+']'
      exec('alter table [dbo].['+@tname+'] drop constraint ['+@dname+']')
      PRINT 'alter table [dbo].['+@tname+'] add constraint [DF_'+@tname+'_'+@cname+'] DEFAULT '+@ddef+' FOR '+@cname
      exec('alter table [dbo].['+@tname+'] add constraint [DF_'+@tname+'_'+@cname+'] DEFAULT '+@ddef+' FOR '+@cname)



No comments:

Post a Comment