The Perils Of sp_rename

July 10th, 2006 Adrian Banks 21605 Views

Andras Belokosztolszki from Red Gate posted an interesting article about the sp_rename stored procedure in SQL Server and the pitfalls that can occur after its use.

When a stored procedure is created, an object is created in the sysobjects table (or the sys.objects view in SQL Server 2005), and the textual definition is stored in the syscomments table (or the sys.sql_modules view in SQL Server 2005).

When using sp_rename to rename a stored procudure, the definition is left intact and only the name is changed in the sysobjects table (or sys.objects view). This means that the definition of the stored procedure stored in database now has the wrong name.

The best way of renaming a stored procedure is to completely delete it and then recreate it with the new name.

The following SQL displays the definitions of all stored procedures in a database alongside their names:

SQL Server 2000

SELECT V2.[name], V1.[text]
    FROM syscomments AS V1, sysobjects AS V2
    WHERE V1.[id] = V2.[id]
    AND (V2.[xtype] = 'P' OR V2.[xtype] = 'F')

SQL Server 2005

SELECT V2.[name], V1.[definition]
    FROM sys.sql_modules AS V1, sys.objects AS V2
    WHERE V1.[object_id] = V2.[object_id]

It is worth noting that enterprise manager uses the sp_rename stored procedure when using the right-click rename option (although SQL Server 2005 management studio does try to correct this problem if you view the definition of a renamed stored procedure by replacing the original stored name with the new one).

Entry Filed Under: SQL Server


Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

Leave a Comment

Required

Required, hidden

Please enter the following word: magic word

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Subscribe to the comments via RSS Feed


Most Popular Posts



Calendar

April 2014
M T W T F S S
« Oct    
 123456
78910111213
14151617181920
21222324252627
282930  

Most Recent Posts

Related Posts

RSS Feeds Feeds

Visitors

Visitor Map

Total Hits: 629726
Hits Today: 2985