Category: SQL Server development
SQL Server versions: 7, 2000, 2005
Level: Intermediate

Naming stored procedures consistently is part of many organizational policies, still there are times when one needs to rename an already existing stored procedure. Seemingly the obvious way to rename a stored procedure would be the sp_rename system stored procedure readily supplied by SQL Server. But this is wrong and should be avoided at all costs!

Stored procedures are stored in SQL Server as textual objects. For example when one creates a stored procedure foo as:

create proc foo
as print 'Foo'

A new object is created in the sysobjects table on SQL Server 2000, and sys.objects system view on 2005. And the definition of the stored procedure is stored in syscomments or sys.sql_modules on 2000 and 2005 respectively. This is very useful, since when one wants to modify a stored procedure one can just use the definition that is included in the database itself.

However, when one renames a stored procedure using sp_rename, the definition is left intact. So sp_rename 'foo', 'bar' will change only the name attribute of the sysobject or sys.objects tables/views.

Consequently, when one uses the definition that is stored in SQL Server, the name of the stored procedure will be incorrect.

The best way of renaming a stored procedure, trigger or function (or DDL trigger on 2005) is to drop the stored procedure and create it again.

Note that SQL Server 2005’s Management Studio, although being really slow, tries to help to solve problems arising from possible previous use of sp_rename. When one right clicks on a stored procedure (or any other textual object) and selects modify or script, the name of the procedure is replaced with the correct fully qualified name. So considering our above example, even though SQL Server still stores the original procedure definition that uses the name “foo”, Management Studio offers it for alter as ‘[dbo].[bar]’

András