Rename all system name restrictions programmatically

If you use a naming convention for each type of constraint, this will help you. Here I am posting an example of how to handle this default constraint. The procedure will find the default constraints, discard those that do not match the naming conventions, and create new ones with the old definition.

This is a simple one-time run version

 DECLARE @SchemaName sysname = 'dbo';
 DECLARE @TableName sysname = NULL;
 DECLARE @ColumnName sysname = NULL;
 DECLARE @sql VARCHAR(max) = '';

SELECT
@sql += 
  'ALTER TABLE [' + s.name + '].[' + o.name + '] DROP CONSTRAINT [' + dc.name + ']; ' + 
  'ALTER TABLE [' + s.name + '].[' + o.name + '] ADD  CONSTRAINT [DF_' + o.name + '_' + c.name + '] DEFAULT ' + dc.DEFINITION + ' FOR [' + c.name + '];'

  FROM dbo.sysobjects do
        INNER JOIN dbo.sysobjects o ON do.parent_obj = o.id
        INNER JOIN sys.default_constraints dc ON dc.object_id = do.id
        INNER JOIN sys.columns c ON c.object_id = o.id
                   AND c.column_id = dc.parent_column_id
        INNER JOIN sys.schemas s ON s.schema_id = dc.schema_id
  WHERE o.type = 'U' 
       AND do.type = 'D'
       AND s.name = @SchemaName
       AND o.name = CASE WHEN ISNULL(@TableName,'') = '' 
                        THEN o.name 
                        ELSE @TableName END
       AND c.name = CASE WHEN ISNULL(@ColumnName,'') = ''
                         THEN c.name 
                         ELSE @ColumnName END
       AND do.name NOT LIKE 'DF_' + o.name + '_' + c.name
  ORDER BY o.name
 --PRINT (@sql)
  EXECUTE(@sql)

      

        
        
        
      

    

And this is the procedure in case you want to continue the procedure every time after a while.

USE [YourDatabaseName]
    GO
    -- Check if the procedure exists and drop it if so
    IF  EXISTS (SELECT * 
                FROM sys.objects 
                WHERE object_id = OBJECT_ID(N'[dbo].[usp_NamingConventionDefaultConstraint]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[usp_NamingConventionDefaultConstraint]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --procedure can be used for specific schema/table/columns or those can be left null to include everything. 
    CREATE PROC [dbo].[usp_NamingConventionDefaultConstraint]
                @SchemaName sysname = 'dbo',
                @TableName sysname = NULL,
                @ColumnName sysname = NULL
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @sql VARCHAR(max)

        DECLARE GetIndexes CURSOR
        FOR
            SELECT
 --here drop the old constraint and create a new one with the old definition
            'ALTER TABLE [' + s.name + '].[' + o.name + '] DROP CONSTRAINT [' + dc.name + ']; ' + 
            'ALTER TABLE [' + s.name + '].[' + o.name + '] ADD  CONSTRAINT [DF_' + o.name + '_' + c.name + '] DEFAULT ' + dc.DEFINITION + ' FOR [' + c.name + '];'
            AS dc_alter
            FROM dbo.sysobjects do
            INNER JOIN dbo.sysobjects o ON do.parent_obj = o.id
            INNER JOIN sys.default_constraints dc ON dc.object_id = do.id
            INNER JOIN sys.columns c ON c.object_id = o.id 
                                     AND c.column_id = dc.parent_column_id
            INNER JOIN sys.schemas s ON s.schema_id = dc.schema_id
            WHERE o.type = 'U' 
            -- work only on default constraints 
            AND do.type = 'D'
            AND s.name = @SchemaName
            AND o.name = CASE WHEN ISNULL(@TableName,'') = '' THEN o.name ELSE @TableName END
            AND c.name = CASE WHEN ISNULL(@ColumnName,'') = '' THEN c.name ELSE @ColumnName END
            -- here goes the naming convention you have in mind
            --DF_TableName_ColumnName
            AND do.name NOT LIKE 'DF_' + o.name + '_' + c.name
            ORDER BY o.name

        OPEN GetIndexes

        FETCH NEXT FROM GetIndexes
        INTO @sql   

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF  ISNULL(@sql,'')<>''
            BEGIN
                EXEC (@sql)
            END

            FETCH NEXT FROM GetIndexes
            INTO @sql   
        END

        CLOSE   GetIndexes
        DEALLOCATE  GetIndexes

    END
    GO