SQL Server Portal

Today, we will discuss about the Do’s and Don’ts of SQL SERVER- System Stored Procedure -“sp_rename”. This procedure is used to change the name of any object created by user.

Syntax :

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

Do’s for Sp_Name :
Given below are the transactions that you can do via sp_rename.

• Rename a table
• Rename a column
• Rename an index
• Rename constraints

Don’ts for Sp_Name :
Given below are the transactions that you must not do via sp_rename.

• Donot Rename a stored procedure
• Donot Rename a function
• Donot Rename a view
• Donot Rename a trigger

Syntax and example of Do’s of Sp_rename are available here.

Lets discuss why we must not rename stored procedure,function,view & trigger via sp_rename.

Lets create a table and view to demonstrate.

Create table tbl_Sample
([Sno] int,
[Dept_Name] varchar(50)
)
Go
Create View vw_Sample
As
Select * from tbl_Sample

Lets run the given below query to check that each and every references are updated.

Select A.[object_id],B.[object_id],A.[name],[Definition]
from sys.Objects  A
Inner Join sys.sql_modules B on A.[object_id]=B.[object_id]
And A.[Type]='V'

You can see that name and definition column matched with respect to name.
Now, lets rename the view.

USE Test
GO
--Syntax sp_rename 'Old View name', 'New view Name'
EXEC sp_rename 'dbo.vw_Sample', 'vw_Sample2';

Lets check again if the references are updated.

Select A.[object_id],B.[object_id],A.[name],[Definition]
from sys.Objects  A
Inner Join sys.sql_modules B on A.[object_id]=B.[object_id]
And A.[Type]='V'

You can see that if you rename view the reference(Definition column of sys.sql_modules) is not updated because of this SQL Server recommends not to rename these objects via sp_rename. Always use drop and create to rename these objects.

Read Full Post »