Home » SP_RENAME in SQL Server

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 we are changing the name of any object it may stop the working of any stored procedures ,views ,triggers or any other objects as Sp_NAME does not update object automatically if object is being referenced by any other objects so in this case,we have to manually update the object name .

SYNTAX

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

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 .

 EXEC SP_RENAME 'dbo.ZXY' , 'XYZ'

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

Selectfrom ZXY

We see , it returns invalid object error, that means table ZXY has been renamed to XYZ.

Now we select records from table XYZ  and  we 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 , we have a column Name that will be renamed to EmpName.

Selectfrom XYZ

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

Now we check table XYZ  to ensure that column NAME is renamed to EmpName or not.

Selectfrom XYZ

Renaming an Index using SP_RENAME

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

CREATE NONCLUSTERED INDEX idx_colEmpName
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 using SP_HELP statement.

 Sp_Help XYZ

Recommended for you

SQL Sp_help

SQL sp_executesql

 




Leave a Reply

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