Home » SQL Server » SP_HELPTEXT showing old Store Procedure name after rename, is it a Bug?

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)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 202 other followers

%d bloggers like this: