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).
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
SQL Server 2005
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).