Normalize and Rename Database Constraints

-- =========================================================================
-- Constraints Renaming
--
-- The following script renames the constraints with the following naming
-- conventions:
--
--  Primary Keys...........: PK_TableName
--
--  Foreign Keys...........: FK_TableName_ReferencedTableName[_ColumnName]
--                           (Column name is only included when a table is
--                           referenced more than once)
--
--  Defaults...............: DF_TableName_ColumnName
--
--  Unique Constraints.....: UQ_TableName[_Num]
--                           (Adds a numeral if more than one UQs exist in a table)
--
--  Check Constraints......: CK_TableName_ColumnName
-- =========================================================================

 use <DATABASE_NAME>;
 go

 -- --------------------------------------
-- Set @dryRun value to "0" (false) to the
-- perform the actual constraint rename
-- operations. "1" (true) will simply print
-- the sql statement to output.
--------------------------------------
declare @dryRun bit = 1;

-- --------------------------------------
--  To List Current Database Constraints
-- --------------------------------------
-- select object_id, type, name
-- from sys.objects
-- where type in('C ', 'PK', 'UQ', 'F ', 'D ');
-- --------------------------------------

--
-- MAIN
--

declare @SqlText varchar(600);

declare RenamingCur cursor local static read_only forward_only
for
    -- Primary Keys
    select 'EXEC sp_rename '+QUOTENAME(d.name, '''')+', '+QUOTENAME('PK_'+OBJECT_NAME(d.parent_object_id), '''')+', ''OBJECT'';'
    from sys.key_constraints as d
    where type = 'PK'
    union all

    -- Foreign Keys
    select 'EXEC sp_rename '+QUOTENAME(f.name, '''')+', '+QUOTENAME('FK_'+OBJECT_NAME(f.parent_object_id)+'_'+OBJECT_NAME(f.referenced_object_id)+
        case
            when COUNT(*) over(partition by f.parent_object_id, f.referenced_object_id) > 1
                then '_'+COL_NAME(fc.parent_object_id, fc.parent_column_id)
            else ''
        end, '''')+', ''OBJECT'';'
    from sys.foreign_keys as f
    join sys.objects as r
        on f.referenced_object_id = r.object_id
    join sys.foreign_key_columns as fc
        on f.object_id = fc.constraint_object_id
    union all

    -- Defaults
    select 'EXEC sp_rename '+QUOTENAME(d.name, '''')+', '+QUOTENAME('DF_'+OBJECT_NAME(d.parent_object_id)+'_'+COL_NAME(d.parent_object_id, d.parent_column_id), '''')+', ''OBJECT'';'
    from sys.default_constraints as d
    union all

    -- Unique Constraints
    select 'EXEC sp_rename '+QUOTENAME(d.name, '''')+', '+QUOTENAME('UQ_'+OBJECT_NAME(d.parent_object_id)+ISNULL(NULLIF('_'+CAST(ROW_NUMBER() over(partition by d.parent_object_id order by i.column_id) as varchar(3)), '1'), ''), '''')+', ''OBJECT'';'
    from sys.key_constraints as d
    join sys.index_columns as i
        on d.parent_object_id = i.object_id
           and d.unique_index_id = i.index_id
           and key_ordinal = 1
    where d.type = 'UQ'
    union

    -- Check Constraints
    select 'EXEC sp_rename '+QUOTENAME(d.name, '''')+', '+QUOTENAME('CK_'+OBJECT_NAME(d.parent_object_id)+'_'+COL_NAME(d.parent_object_id, d.parent_column_id), '''')+', ''OBJECT'';'
    from sys.check_constraints as d;

--
-- cursor for renaming each constraint:
open RenamingCur;
fetch next from RenamingCur into @SqlText;
while @@FETCH_STATUS = 0
begin
    if isnull(@dryRun, 1) = 0
        begin
            exec (@SqlText);
        end;
    else
    begin
        if isnull(@dryRun, 1) = 1
            begin
                -- dry run, just print sql statement to output
                 print @SqlText;
            end;
    end;
    fetch next from RenamingCur into @SqlText;
end;
close RenamingCur;
deallocate RenamingCur;