SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SP_HELPTEXT showing old Store Procedure name after rename, is it a Bug?

Question : Today morning, I received a call from one of my old friend, with an query for SQL Server. He is sharing a problem with Sp_Helptext. He is considering it as a Bug.

Try to rename any procedure in your database by right click. Then modify it. SSMS will show changed name. But when you write sp_helptext <changedProcName> then it will show you the previous name.

Answer : I do not think it’s a bug. Its normal. This happens because when the store procedure is renamed, the sys.syscomments system table is not getting updated.

SQL statement shown by sp_helptext is from sys.syscomments. This system view return the script run by you at the time of object creation.

1. When you change SP name from GUI, you have not edit the script, you just rename the tittle. Due to which there is no change in sys.syscomments & you got old name when you run sp_helptext <changedProcName>

2. When you perform it from T-SQL script, you actually re-run the procedure code & sys.syscomments got updated (by Alter command or by drop & recreate the procedure).

select * from sys.syscomments where object_name(id) = ‘<changedProcName>’

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name. (http://msdn.microsoft.com/en-us/library/ms188351(SQL.90).aspx)


I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.


Leave a comment on the original post [mssqlfun.com, opens in a new window]

Loading comments...