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)
