Rename

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.

The Syntax

The syntax for is like this:

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

The Table

For example you have a table created with this script

CREATE TABLE [dbo].[t1](
		[Id] [int] IDENTITY(1, 1) NOT NULL,
		[Sequence] rowversion,
		CONSTRAINT PRIMARY KEY (Id)
	)

In this case the database will generate a Primary index with an random number letter combination in the name.

How to rename

Now we wan't to rename this Key. First, let's get a primary key name.

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__334448797999944  |
 +--------------+--------------------------+ 

You see that the server generated the key name PK__t1__334448797999944

No we will go ahead and rename this key with the sp_rename command

Important! When you rename a key or an object, you MUST include the schema name with the current name.

Example:

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

When you execute this, the new Keyname will be now set as PK_t1.

You will also notice a warning like this

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

It will remeber us, that any script, that will referncing on this keyname, will now break. Nice.. after the execution of this script, it will remind us... you see the error eh? 😁

Anyway, when you check the key again with the script above

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

You will get the result:

+--------------+--------------------------+
 | SchemaName   | PrimaryKey               |
 |--------------+--------------------------|
 | dbo          | PK__t1                   |
 +--------------+--------------------------+ 

Hope it helped someone. Leave me a comment if you have any suggestions.