Skip to content
Home » SP_RENAME in SQL Server


SQL Server SP_RENAME is SQL Server Database Engine stored procedure that is used to changes the name of a user-created object in the current database. This object can be a table, index name, column or alias datatype.

If you are changing the name of any object it may stop the working of any stored procedures, views ,triggers or any other objects as sp_rename does not update object automatically if object is being referenced by any other objects so in this case, you will have to manually update the object name .


EXEC sp_rename [ @object_name = ] 'object_name' , 
[ @new_name = ] 'new_name' [ , [ @object_type = ] 'object_type' ]

[ @object_name = ] ‘object_name’
Is the current user object or data type.

If the object to be renamed is a column in a table, we use table.column or schema.table.column.

If the object to be renamed is an index then table.index or schema.table.index.

[ @new_name = ] ‘new_name’
Is the new name for the specified object. new_name must be a one-part name .

[ @object_type = ] ‘object_type’
Is the type of object being renamed. The size for object_type is varchar(13) with a default of NULL.

To demonstrate this, first we will create a table and insert some dummy records in table.

Create table ZXY (Sno int , Name varchar(50))

Insert into ZXY values(1, 'Anthony Carlos') 
Insert into ZXY values(2, 'Mark Jr')
Insert into ZXY values(3, 'Jackson Morey')

Select * from ZXY

Change a user created table name using SP_RENAME

Following statement uses SP_RENAME system stored procedures to rename a table from

ZXY to XYZ .


Now we try to select records from a table ZXY to see if it still exists or renamed .

Selectfrom ZXY

You can see, it returns an invalid object error which means table ZXY has been renamed to XYZ.

Lets select records from table XYZ and you can see it returns same records that means table ZXY name is changed to XYZ.

Renaming a table column using SP_RENAME

Following statement uses SP_RENAME system stored procedures to renames the column NAME in  a table XYZ to EmpName.

As you can in table XYZ,  now there is one column named as Name which will be renamed to EmpName.

Selectfrom XYZ

 EXEC SP_RENAME 'dbo.XYZ.Name' , 'EmpName','COLUMN';

Lets check table XYZ  to ensure that column NAME is renamed to EmpName.

Selectfrom XYZ

Renaming an Index using SP_RENAME

First we will create an index on column EmpName in table XYZ.

ON XYZ(EmpName)

Here index name or Id is – idx_colEmpName, now we will change index name to idx_EmpName.

EXEC SP_RENAME 'dbo.XYZ.idx_colEmpName', 'idx_EmpName', 'INDEX';

Lets check, whether an index name is changed or not by using SP_HELP statement.

 Sp_Help XYZ

Also Read..

SQL Sp_help

SQL sp_executesql



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.