We prefer to name all primary key constraints explicitly and not let SQL server come up with its own names. In this case:
- We always know what name to look for.
- 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
Recent Comments