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_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

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

Also Read..

SQL Sp_help

SQL sp_executesql




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 602 total views,  1 views today

Leave a Reply

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