Tuesday, June 22, 2010

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
procedure SP1 as select 1 as x

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!

1 comment:

  1. recently experienced the same issue but with renaming sql server views. Instead of using the proc for renaming my views - I used F2 in SSMS. SP_helptext proc was still showing me an incorrect view definition.