I came across this behavior a while ago and decided to write about it, so it may prevent you from falling into the same trap.
It’s very straight to reproduce. First, let’s create a simple stored procedure:
This procedure doesn’t do anything useful but that’s not the point here. Let’s rename the procedure by utilizing the system stored procedure sp_rename like this:
exec sp_rename 'SP1','SP2'
After invoking the statement above, our procedure SP1 will have been renamed to SP2. You may check this by calling the procedure.
Now, let’s see what sp_helptext returns for SP2:
exec sp_helptext 'SP2'
Here’s the result:
Oops. Wrong (old) name in the CREATE PROCEDURE statement.
So, how about querying sys.sql_modules then:
select object_name(object_id) as sp_name, definition
This query will return the following result:
Also not the correct CREATE PROCEDURE statement.
I was right about adding an item on Microsoft’s connect platform for reporting this behavior as a bug, when I discovered this warning in books online regarding sp_rename:
So, the behavior seems to be as expected. Be careful with sp_rename – not only when renaming stored procedures but also triggers, functions and views!