Hello Madhivanan,

This is nice tip. This is becuase of it just renamed the name of stored procedure but not change name in text of syscomments. It is recommended to generate stored procedures text from managament studio, so it will have correct text as well it will retain the stored procedures settings as i have discussed some issues using SP_HELPTEXT in one of my post here, http://beyondrelational.com/justlearned/posts/234/take-care-while-using-sphelptext-to-get-stored-procedure-text-in-sql-server.aspx

Is it better to drop and recreate procedure to be sure?

Hello Madhivanan,

The problem is well-known since SQL Server 2005 ( 2000 maybe ). For some explanations, please, could you have a look at this link ? http://connect.microsoft.com/SQLServer/feedback/details/272946/sp-rename-doesnt-refactor-dependencies With my knowledges about the SQL Server Management Team, this problem will always be there in the versions appearing after 2015. The solution is given in this link to drop and recreate the stored procedure : a solution simple for Microsoft but stupid for a DBA It seems you will find the explanation with this link : http://social.msdn.microsoft.com/Forums/en/sqlkjmanageability/thread/91f982e7-caac-4c36-a52c-600588f29a09 Even if the thread is related to the rename of a table and not reported in the stored procedure using this table, i think the explanation could be the same.

Hello,

This is a valuable tip. I wanted to also add that if you rename procedures through the Management Studio UI, it uses sp_rename in the background. To my knowledge to only way to get the help text updated is to drop and recreate the object. Probably not a huge deal for procedures, functions, etc... but potentially a big PITA when the need to rename tables arises.

@tokarski:

Per Books-On-Line: http://msdn.microsoft.com/en-us/library/ms188351.aspx

"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."