WikiDBA

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.