Renaming a stored procedure may lead to inconsistent system catalogs

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:

use tempdb go create procedure SP1 as select 1 as x go

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   from sys.sql_modules where object_id=object_id('SP2')

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!