SP_HELPTEXT showing old Store Procedure name after rename

Posted: March 14, 2014 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,

Today morning, my one friend call me, saying there is a bug with SQL Server. As per his statement after renaming a Stored procedure name from SSMS, SP_HELPTEXT showing old name in script while SSMS is showing changed name, I think it’s not a system bug because its happens when the store procedure is renamed using right click and rename in GUI or using SP_rename <object name>, the sys.syscomments system table is not getting updated and SQL statement shown by sp_helptext is from sys.syscomments. by using ALTER statement sys.syscomments get updated. So, 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, it’s recommend that sp_rename/or from GUI, should not be used to rename these object types. We can use alter statement or drop and re-create the object with its new name.

Advertisements
Comments
  1. Prafull says:

    It was really nice information

    Like

  2. Ameer khan says:

    It’s good article. MS should have deprecated sp_rename…

    Like

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