Rename Stored Procedure in SQL Server

As a database developer, you sometimes need to rename stored procedure in SQL Server as business logic changes.

So, in this SQL Server tutorial, I will show you the steps to rename the existing stored procedure in your database, which is an essential skill every SQL Server developer must have.

You will understand the syntax you can use to rename the stored procedure. Also, you will know if the dependent object gets affected when you change the rename of the database.

Finally, you will create and rename a stored procedure using sp_rename stored procedure in SQL Server.

Rename Stored Procedure in SQL Server

There are multiple reasons to rename stored procedures in SQL Server, such as aligning with updated naming conventions to reflect changes in business logic.

The SQL Server provides a stored procedure called sp_rename that renames any stored procedure.

The syntax is given below.

EXEC sp_rename 'old_procedure_name', 'new_proecdure name;

Where,

  • EXEC sp_rename: This is the command to rename the stored procedure.
  • old_procedure_name: Name of the old stored procedure whose name you want to change.
  • new_procedure_name: The new name of the stored procedure you wish to assign.

The new name of the stored procedure must agree with the rules for identifiers. When stored procedures are renamed, object_id and all the permissions assigned to stored procedures are unaffected.

Also, when the name of the stored procedure is changed, it doesn’t change the corresponding object name in the definition column of sys.sql_modules catalogue view.

When the procedure’s name changes, all the dependent objects fail because the changes made to the stored procedure are not reflected with objects.

Rename Stored Procedure in SQL Server: Create a new Stored Procedure

My database has a table of Employees with EmployeeID, Name, Salary, and Department.

I have created a stored procedure to retrieve all the data from the Employees using the command below.

CREATE PROCEDURE GetEmployees
AS
BEGIN
	SELECT * FROM Employees;
END;
Creating a new Stored Procedure Before Renaming it

Let’s call the stored procedure to see if it is working properly.

EXECUTE GetEmployees;
Executing Stored Procedure

The stored procedure is working correctly; now,

Rename Stored Procedure in SQL Server

You have to change the stored procedure name from GetEmployees to usp_GetEmployees.

The sp_rename stored procedure can be used, as shown in the query below.

EXECUTE sp_rename 'GetEmployees', 'usp_GetEmployees';

When executing the above command, it changes the name of the store procedure from GetEmployees to usp_GetEmployees;

So first, we will call the stored procedure with the old name using the command below.

EXECUTE GetEmployees;
Executing Old Stored Procedure

You get this error because we changed the name of the stored procedure, so it doesn’t exist.

Let’s call with a new name using the below command.

EXECUTE usp_GetEmployees;
Rename Stored Procedure in SQL Server

Now, the stored procedure with name returned the Employees table data. This is how to rename stored procedure in SQL Server using the sp_rename stored procedure.

Also, I suggest that when you have stored procedures within a specific schema, you can follow the syntax below.

EXEC sp_rename 'currentSchema.old_procedure_name', 'new_proecdure name;

Here, you need to prefix the procedure name with the schema where it exists. For example, let’ the GetEmployees procedure exist in the ’emp’ schema, then change its name. You can follow the below query.

EXECUTE sp_rename 'emp.GetEmployees', 'usp_GetEmployees';

Look, emp.GetEmployees means accessing the GetEmployees procedure in the schema emp and changing its name to usp_GetEmployees;

This is how to rename stored procedures in the specific schema in SQL Server.

Conclusion

You learned how to use the sp_rename stored procedure to rename stored procedure in SQL Server.

Where you have changed the stored procedure GetEmployees to usp_GetEmployees.

You renamed the stored procedure, which exists in default and specific schema.

You may like to read: