Rename a Stored Procedure

This topic describes how to rename a stored procedure in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To rename a stored procedure, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• Procedure names must comply with the rules for identifiers.

• Renaming a stored procedure will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that you do not rename this object type. Instead, drop and re-create the stored procedure with its new name.

• Changing the name or definition of a procedure can cause dependent objects to fail when the objects are not updated to reflect the changes that have been made to the procedure. For more information, see View the Dependencies of a Stored Procedure.

Security

Permissions

CREATE PROCEDURE
Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

ALTER PROCEDURE
Requires ALTER permission on the procedure or requires membership in the db_ddladmin fixed database role.

Using SQL Server Management Studio

To rename a stored procedure

1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

3. Determine the dependencies of the stored procedure.

4. Expand Stored Procedures, right-click the procedure to rename, and then click Rename.

5. Modify the procedure name.

6. Modify the procedure name referenced in any dependent objects or scripts.

Using Transact-SQL

To rename a stored procedure

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example shows how to rename a procedure by dropping the procedure and re-creating the procedure with a new name. The first example creates the stored procedure 'HumanResources.uspGetAllEmployeesTest. The second example renames the stored procedure to HumanResources.uspEveryEmployeeTest.

--Create the stored procedure.  
USE AdventureWorks2012;  
GO  
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployeesTest', 'P' ) IS NOT NULL   
    DROP PROCEDURE HumanResources.uspGetAllEmployeesTest;  
GO  
CREATE PROCEDURE HumanResources.uspGetAllEmployeesTest  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory;  
GO  
  
--Rename the stored procedure.  
USE AdventureWorks2012;  
GO  
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployeesTest', 'P' ) IS NOT NULL   
    DROP PROCEDURE HumanResources.uspGetAllEmployeesTest;  
GO  
CREATE PROCEDURE HumanResources.uspEveryEmployeeTest  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory;  
GO  

See Also

ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
Create a Stored Procedure
Modify a Stored Procedure
Delete a Stored Procedure
View the Definition of a Stored Procedure
View the Dependencies of a Stored Procedure