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 .
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 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 .
EXEC SP_RENAME 'dbo.ZXY' , 'XYZ'
Now we try to select records from a table ZXY to see if it still exists or renamed .
Select * from 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.
Select * from XYZ
EXEC SP_RENAME 'dbo.XYZ.Name' , 'EmpName','COLUMN';
Lets check table XYZ to ensure that column NAME is renamed to EmpName.
Select * from XYZ
Renaming an Index using SP_RENAME
First we will 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 by using SP_HELP statement.
Sp_Help XYZ
Also Read..
