In SQL Server, you can use the sp_rename stored procedure to rename a user created object in the current database, including a primary key.

This can be handy if you’ve got a primary key that had its name automatically assigned, and you now want to give it a more readable name.

When you create a primary key without explicitly providing a name for it, SQL Server automatically delegates a name for it. Such names typically include a long numeric suffix, which makes it harder to remember. If you need to refer to that primary key (e.g. in your code, documentation, etc), such names can make your life more difficult. Fortunately, sp_rename provides a quick and easy way to change this name.

Syntax

The syntax for sp_rename goes like this:

sp_rename 
    [ @objname = ] 'object_name' ,
    [ @newname = ] 'new_name'
    [ , [ @objtype = ] 'object_type' ]

For primary keys (and other constraints), the object type is OBJECT.

Example

First, let’s get the name of the primary key that we want to change:

SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS PrimaryKey
FROM sys.objects  
WHERE parent_object_id = (OBJECT_ID('dbo.t1'))   
AND type = 'PK';

Result:

 +--------------+--------------------------+
 | SchemaName   | PrimaryKey               |
 |--------------+--------------------------|
 | dbo          | PK__t1__3213663B10938530 |
 +--------------+--------------------------+ 

This is the primary key for the table called t1, So we’ll go ahead and rename that primary key.

When you rename a primary key, you need to include the schema with the current name.

Example:

EXEC sp_rename 'dbo.PK__t1__3213663B10938530', 'PK_t1';

When you rename a primary key (or any other object) in SQL Server, you’ll probably see the following message:

Caution: Changing any part of an object name could break scripts and stored procedures.

This is basically giving us the heads up that any scripts and/or stored procedures that reference the object could now break, and should be updated accordingly to reflect the new name.

Regardless, the primary key has now been renamed.

We can verify this by running the previous query again:

SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS PrimaryKey
FROM sys.objects  
WHERE parent_object_id = (OBJECT_ID('dbo.t1'))   
AND type = 'PK';

Result:

 +--------------+--------------+
 | SchemaName   | PrimaryKey   |
 |--------------+--------------|
 | dbo          | PK_t1        |
 +--------------+--------------+ 

Including the Object Type

You can also include the object type as a third argument. For primary keys and other constraints, use OBJECT:

EXEC sp_rename 'dbo.PK__t1__3213663B10938530', 'PK_t1', 'OBJECT';

Including the Parameter Names

As with any stored procedure, you can also include the parameter names when calling sp_rename:

EXEC sp_rename 
    @objname = 'dbo.PK__t1__3213663B10938530',
    @newname = 'PK_t1',
    @objtype = 'OBJECT';

This does exactly the same thing (renames the specified primary key).