Renaming Primary Keys to Standard

We prefer to name all primary key constraints explicitly and not let SQL server come up with its own names. In this case:

1. We always know what name to look for.
2. Standard name, which in our case looks like PK_tableName, simplifies automatic maintenance of primary keys: if all names are standard, they can be used in stored procedures and triggers with no need of lookup in metadata, which significantly simplifies maintenance SQL scripting.

Script below goes through the list of database primary keys and renames them all to standard. We recommend to run this procedure once in a while manually or as a maintenance job, because sometimes users forget about explicit naming of constraints and default constraint names assigned by SQL server tend to accumulate in the database.

You can download the script from Dropbox location, no need to copy/paste it.

IF EXISTS (
 SELECT 1 FROM sys.objects
 WHERE name='usp_Util_RenameConstraintsPK')
 DROP PROCEDURE [dbo].[usp_Util_RenameConstraintsPK]
 GO

CREATE PROCEDURE [dbo].[usp_Util_RenameConstraintsPK]
 AS
 BEGIN
 SET NOCOUNT ON;

DECLARE @constraintName NVARCHAR(4000)
 DECLARE @tableName NVARCHAR(100)
 DECLARE @colName NVARCHAR(100)
 DECLARE @newName NVARCHAR(4000)

DECLARE BADPK CURSOR FOR
 SELECT cons.name, tab.name, col.name
 FROM sys.key_constraints cons, sys.index_columns indcol,
 sys.columns col, sys.tables tab, sys.indexes idx
 WHERE cons.parent_object_id = indcol.object_id
 AND cons.parent_object_id = tab.object_id
 AND cons.type='PK'
 AND cons.parent_object_id = col.object_id
 AND indcol.column_id = col.column_id
 AND indcol.is_included_column=0
 AND indcol.object_id = idx.object_id
 AND indcol.index_id = idx.index_id
 AND idx.type = 1
 AND indcol.column_id = 1
 ORDER BY tab.name, col.name, indcol.index_column_id

DECLARE @q VARCHAR(4000)
 DECLARE @prevConstraintName NVARCHAR(100)
 DECLARE @prevTableName NVARCHAR(100)

OPEN BADPK
 FETCH NEXT FROM BADPK INTO @constraintName,
 @tableName, @colName

WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @newName = 'PK_' + @tableName
 SET @q = 'EXEC sp_rename ''' + @constraintName +
 ''', ''' + @newName + ''''
 PRINT @q
 EXEC (@q)
 FETCH NEXT FROM BADPK INTO @constraintName,
 @tableName, @colName
 END

CLOSE BADPK
 DEALLOCATE BADPK
 END
 GO