How to Rename Primary Key Constraint for Multiple Tables in SQL Server Database - SQL Server / TSQL Tutorial Part 64

Scenario:

You are working as SQL Server Developer or SQL Server DBA. You are working on naming standards for database objects. You noticed that in one of the database the Primary Key Constraints are not following your company naming convention standards that is "Pk_SchemaName_TableName_ColumnName" as shown below in red.

How to rename Primary Key Constraint for Multiple Tables in SQL Server Database - SQL Tutorial

Solution:

First of all we need to find the Primary Key Constraints which do not follow naming convention standards. Every company can have different naming convention document. You company might not include Schema Name in Primary Key Constraint. You can change the select query and remove Table_Schema from below script.

USE YourDatabaseName
GO
--Declare Variables
DECLARE @DatabaseName AS VARCHAR(128)
DECLARE @SchemaName AS VARCHAR(128)
DECLARE @TableName AS VARCHAR(128)
DECLARE @ColumnName AS VARCHAR(128)
DECLARE @ConstraintName AS VARCHAr(128)

DECLARE CUR CURSOR
FOR
--Get Primary Key Constraint
Select
   TC.Table_Catalog as DatabaseName,
   TC.Table_Schema AS TableSchema,
   TC.Table_Name AS TableName,
   CCU.Column_Name AS ColumnName,
   TC.Constraint_Name AS ConstraintName
  From
   information_Schema.Table_Constraints TC  
INNER JOIN
   Information_Schema.constraint_column_usage CCU  
      on TC.Constraint_Name=CCU.Constraint_Name  
      and TC.Table_Name=CCU.Table_Name  
where
   Constraint_Type='PRIMARY KEY'
--Change your criteria as per your naming convention 
   and 'Pk_'+TC.Table_Schema+'_'+TC.Table_Name
   +'_'+CCU.Column_Name<>TC.Constraint_Name
OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName,@SchemaName,@TableName,@ColumnName,

@ConstraintName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Build dynamic sql for each database 
    DECLARE @SQL VARCHAR(MAX) = NULL
   SET @SQL ='sp_rename '+Char(10)
   SET @SQL+='@objname ='''+@SchemaName+'.'+@ConstraintName+''',' 
   SET @SQL+='@newname='''+'Pk_'+@SchemaName
   SET @SQL+='_'+@TableName+'_'+@ColumnName+''','
   SET @SQL+='@objtype = ''object'''
    --EXEC (@SQL)
    PRINT @SQL
    FETCH NEXT
    FROM Cur
    INTO @DatabaseName,@SchemaName,@TableName,@ColumnName,

@ConstraintName
END
CLOSE Cur
DEALLOCATE Cur

I executed above script on one of my database and here are printed statements those I see for two Primary Key Constraints which were not following naming standards as per my company standards.

How to rename Primary Key Constraints for multiple SQL Server Tables in SQL Database 

The Primary Key Constraints were renamed successfully as shown below.

How to verify and rename Primary Key Constraints in SQL Server Database in SQL Server