Issue with renaming the objects using Management Studio 2005 | SansSQL

Wednesday, October 15, 2008

Issue with renaming the objects using Management Studio 2005

Whenever we rename an object like stored procedure, function, view or trigger using the management studio in SQL server 2005, the syscomments table will not be updated. Hence when we try to get the text/code of the renamed object using the sp_helptext or OBJECT_DEFINITION () we will get the text/code of the object with the old name itself.

Try it yourself:

Use AdventureWorks
Go
CREATE PROC TestRename AS
Print ‘XYZ’
GO

Now, rename this SP using the management studio object list to “RenameTest”
Now Execute the below Query on AdventureWorks Database.

sp_helptext RenameTest

Now if you observer the result pane, the result will be the same statement with the procedure name still not changed even after renaming the SP using the management studio. The result will be as shown below.

CREATE PROC TestRename AS
Print 'XYZ'

Ads