Views & Stored Procs

Strange behaviour on using sp_rename procedure in Views ,Procedures & Functions


sp_rename takes in the parameter of Old object name and new object name.

So to rename a table authors in pubs DB we would use


sp_rename 'authors','authors_new' and the entry is updated in sysobjects name column for the
Specific Object table Details and name are stored in only sysobjects hence a update was made effectively by SQL when sp_rename function is executed

Lets see the same in case of Views and Stored Procedures
I have view named vw_authors and I have just select * from authors As

Create View vw_authors
As
select * from authors

Now I rename the same to vw_authors_new then it would change the same in sysobjects table and it I will no longer be able to refer the same with vw_authors

But what happens when I use sp_helptext and view the contents of the vw_authors_new it still shows As

Create View vw_authors
As
select * from authors

Why is this happening?

Its because the update of name happens only in sysobjects table and not in syscomments table.
Syscomments has the definition of view or procedure in column named text and hence the same cannot be updated in the column with the new name.To avoid this do not rename views or procedures,always drop and create the same to have consistency between sysobjects and syscomments