In SQL, how do you change the name of a stored procedure?

Question

Aryan Kumar · Answer

There are two ways to change the name of a stored procedure in SQL:

Using the sp_rename system stored procedure:

The sp_rename system stored procedure can be used to rename any object in a SQL Server database, including stored procedures. The syntax for the sp_rename system stored procedure is:

The old_stored_procedure_name is the name of the stored procedure that you want to rename. The new_stored_procedure_name is the new name for the stored procedure.

For example, the following statement will rename the stored procedure GetCustomers to GetCustomers2:

SQL

sp_rename 'GetCustomers', 'GetCustomers2';

SQL

sp_rename 'old_stored_procedure_name', 'new_stored_procedure_name';

Using the ALTER PROCEDURE statement:

The ALTER PROCEDURE statement can be used to change the name of a stored procedure, as well as other properties of the stored procedure. The syntax for the ALTER PROCEDURE statement is:

The new_stored_procedure_name is the new name for the stored procedure. The BEGIN and END keywords delimit the body of the stored procedure.

For example, the following statement will rename the stored procedure GetCustomers to GetCustomers2 and also change the body of the stored procedure:

It is important to note that you cannot rename a stored procedure that is being used by another object, such as a view or a stored procedure.

Here are some additional things to keep in mind when renaming a stored procedure:

The sp_rename system stored procedure is a Transact-SQL statement. Transact-SQL statements are not reversible, so it is important to make sure that you are renaming the correct stored procedure.
You cannot rename a stored procedure that is being used by another object, such as a view or a stored procedure.
If you are renaming a stored procedure that is a user-defined function, you need to update any references to the stored procedure in other objects.

SQL

ALTER PROCEDURE GetCustomers2 AS
  BEGIN
    SELECT * FROM Customers;
  END;

SQL

ALTER PROCEDURE new_stored_procedure_name AS
  BEGIN
    ...
  END;

forum

In SQL, how do you change the name of a stored procedure?

Can you answer this question?

1 Answers