Constraints renaming

-------------------------------------------------------------------------------------------------
-- Purpose: The following script renames the constraints with the following naming convention:
--           - 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
-- Author : Luis Cazares
-------------------------------------------------------------------------------------------------
DECLARE @SQL 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 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 f 
JOIN sys.objects 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 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 d 
JOIN sys.index_columns 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 ALL
----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 d;

OPEN RenamingCur;
FETCH NEXT FROM RenamingCur INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@SQL);
    FETCH NEXT FROM RenamingCur INTO @SQL;
END 
CLOSE RenamingCur;
DEALLOCATE RenamingCur;

--SELECT object_id,
--    type,
--    name
--FROM sys.objects
--WHERE type IN( 'C ', 'PK', 'UQ', 'F ', 'D ' )