Renaming Stored Procedures

You can rename a stored procedure using the sp_rename system procedure.

The new name must follow the rules for identifiers. You can rename only the stored procedures that you own. The database owner can change the name of any user's stored procedure. The procedure to be renamed must be in the current database.

You must drop and re-create a stored procedure if you change the name of an object referenced by that procedure. A stored procedure that references a renamed table or view might work fine for a while. In fact, it works until it is recompiled. However, recompilation takes place for many reasons and without notification to the user.

Use the sp_depends system procedure to get a report of the objects referenced by a procedure as shown in Displaying Information About Stored Procedures.

    To rename a stored procedure

• Execute the sp_rename system stored procedure.

  For example:

  sp_rename p1, mary_proc

  renames the p1 procedure created in the preceding section to mary_proc.

  (For details, see the sp_rename system stored procedure in the Microsoft SQL Server Transact-SQL Reference.)