Lets find out why it is not a good idea to rename Stored Procedures\ Triggers \Functions\ Views in SQL Server. I will present the case with Stored Procedures and the same is applicable for Triggers, Functions and Views. The problem in renaming these objects using SP_RENAME command is when we try to read the definition using SP_HELPTEXT or OBJECT_DEFINITION() then we will get the definition of the object with Old Name.
Lets see this with an example:
Lets first create a Stored Procedure:
— Creating the SP to get the info about SalesPersons
CREATE PROCEDURE Original_SalesPerson
SELECT * FROM Sales.SalesPerson
Now, lets rename the SP created above by SP_RENAME Command:
Lets view the text of SP that we have just renamed:
We can see the name of the stored procedure is the old name and not new name. This is because when we rename any Triggers, Views, SPs or Functions then the name of these objects in not updated in the Text column of [SYS].[SYSCOMMENTS] table.
This is the reason I always suggest if for any reason we need to rename the objects it is always better to DROP and CREATE them.