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”.

 

BEGIN TRANSACTION

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 '%[_][_]%')

BEGIN

      select top 1 @tname=t.name, @cname=c.name, @dname=d.name, @ddef=d.definition

      from sys.tables t

            join

            sys.default_constraints d

                  on d.parent_object_id = t.object_id

            join

            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)

END

COMMIT

GO