Rename stored procedure in SQL Server

In the SQL Server tutorial, we will discuss how to rename stored procedure in SQL Server. And we also cover different ways to rename a stored procedure with examples. The list of topics that we will cover is given below.

• Rename stored procedure in SQL Server
• Rename stored procedure in SQL Server using SQL Server Management Studio
• Rename stored procedure in SQL Server using query
• Copy and rename a stored procedure in SQL Server
• Stored procedure to rename table in SQL Server

So, if you are not familiar with the stored procedure first, understand What is a stored procedure in SQL Server. And also How to execute a stored procedure in SQL Server.

Rename stored procedure in SQL Server

In SQL Server, there are mainly two ways to rename a stored procedure. The first method is by using SQL Server Management Studio, and the second method is by using Transact-SQL query.

But before renaming a stored procedure, let’s take a look at some of the limitations and restrictions of renaming a stored procedure in SQL Server.

• The name of the stored procedure should follow the identifier rules. These rules clarify how we should name a stored procedure in SQL Server.
• When we rename a stored procedure, the object_id and permissions associated with that procedure are retained. Whereas if we delete and recreate the stored procedure then, a new object_id is created. Also, all the assigned permissions to that stored procedure are removed.
• When a stored procedure is renamed, the object name in the definition column of the sys.sql modules view does not change. To change that, we need to drop that stored procedure and recreate it with a new name.
• When the name of a procedure is changed, it might cause dependant objects to fail. This usually occurs if the objects are not updated to reflect the changes.

After considering all these limitations, let’s understand how to rename the stored procedure.

• Alter Stored Procedure in SQL Server

Using SQL Server Management Studio

Steps to rename a stored procedure in SQL Server using SQL Server Management Studio are illustrated below.

• First, connect to the Database Engine using the credentials, and then, from the Object Explorer, expand the database instance.
• After this, expand the database in which the stored procedure was created.
• Next, expand the Programmability directory and then, expand Stored Procedures.
• Next, to determine the dependencies of the stored procedure, right-click it and click on “View Dependencies“.

• This will open a new Object Dependencies window where all dependencies related to the procedure will be listed.

• Now, again, right-click the stored procedure and click on Rename. And then, provide the new name for the stored procedure.

• In the end, modify the procedure name in any dependent object or script.

• SQL Server stored procedure case statement

Using Transact-SQL

Now, let’s understand how to rename a stored procedure in SQL Server using a query.

Method: 1

Now, the best way to rename a stored procedure in SQL Server is by dropping it and recreating it with a new name. And this method is also recommended by Microsoft itself. Let’s understand this method with a demonstration of an example.

So, first, we created a simple stored procedure in the sqlserverguides database. This procedure takes the department name as an input parameter and returns all the records of that department. The script for this procedure is as follows.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[GetEmployeesByDept]
( @Dept VARCHAR(20) )
AS
BEGIN
	SELECT Name, gender, Department FROM dbo.Employees
	WHERE Department = @Dept
END
GO

Now, we will drop this stored procedure and create the same stored procedure with a new name. The example for this is given below.

USE [sqlserverguides]
GO

IF OBJECT_ID ('GetEmployeesByDept') IS NOT NULL
   DROP PROCEDURE GetEmployeesByDept
GO
CREATE PROCEDURE GetRecordsByDept
( @Dept VARCHAR(20) )
AS
BEGIN
	SELECT Name, gender, Department FROM dbo.Employees
	WHERE Department = @Dept
END
GO

In the above example, first, we have used the IF statement to check whether the procedure with the given name exists or not. Now, if the procedure exists then, the DROP PROCEDURE statement will be executed to delete that procedure.

And then, we have created a new procedure with a different name but its functionality is the same.

Method: 2

There is one more method that we can use to rename a store procedure in SQL Server. In this method, we will use sp_rename for renaming a stored procedure in SQL Server.

The sp_rename is a system stored procedure that is used to rename the user-created objects in a database.

Let’s take a look at the syntax of using sp_rename in SQL Server.

EXEC sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'   
    [ , [ @objtype = ] 'object_type' ]

The sp_rename accepts 3 input parameters,

• The first is the current name of a user object. Here we need to specify the current procedure name that we want to change.
• The second is the new name for the specified object. Here we need to specify the new name for the stored procedure.
• The third is the type of object that is being renamed. In the case of stored procedures, we don’t need to specify this argument.

Now, let’s understand how to rename the stored procedure using sp_rename. For this demonstration, consider the following example.

USE [sqlserverguides]
GO

EXEC sp_rename 'GetRecordsByDept', 'GetEmployeesByDept'
GO

In the above example, we are renaming a stored procedure with the name “GetRecordsByDept” to the name “GetEmployeesByDept“.

Note: It is not recommended to use this method because it can break scripts and stored procedures. The SQL Server even returns a similar warning while execution. Still, it will rename the specified stored procedure in the database.

Here is the output of the above-illustrated example.

Also, Read: Loop in SQL Server stored procedure

Copy and rename a stored procedure in SQL Server

In the above section, we have seen how to drop and recreate a stored procedure with a new name. But, in that case, we have to manually write the script for the stored procedure.

So, in this section, we will understand how to copy and rename a stored procedure in SQL Server. So that, we don’t need to manually write the script for the new stored procedure.

Now, this task can be implemented in 2 different ways. The first method is by using SQL Server Management Studio, and the second method is by using Transact-SQL query.

• Could not find stored procedure in SQL Server

Using SQL Server Management Studio

Here are some steps to copy and rename a stored procedure using SQL Server Management Studio.

• First, connect to the Database Engine using the credentials, and then, from the Object Explorer, expand the database instance.
• After this, expand the database in which the stored procedure was created.
• Next, expand the Programmability directory and then, expand Stored Procedures.
• After this, right-click on the required stored procedure and select “Script Stored Procedure As” > “CREATE TO” and then click on “New Query Editor Window“. This will generate the script for that stored procedure and open it in a new editor window.

• Now, in the script, we can change the name of the stored procedure and click on execute. This will create a new stored procedure with the specified name and same script.

Using Transact-SQL

Now, in this section, we will understand how to copy and rename a stored procedure in SQL Server using a query. Let’s understand this implementation with the help of an example.

In the example, we will try to copy the script of a stored procedure with the name GetEmployeesByDept. And then, try to create a new stored procedure with the same script and a new name. Now, the query for this example is given below.

DECLARE @NewScript NVARCHAR(500)

SELECT 
    @NewScript = REPLACE( M.Definition, 'GetEmployeesByDept', 'GetRecordsByDept')

FROM sys.sql_modules as M INNER JOIN sys.objects as O 
ON M.object_id = O.object_id 
WHERE O.type = 'P' AND
      O.Name = 'GetEmployeesByDept'

PRINT @NewScript

EXEC sp_executesql @NewScript

• In the above example, we are selecting the definition of a stored procedure whose name is GetEmployeesByDept.
• After this, we are using the REPLACE function to replace the old name with a new one. And we are storing the updated definition in a variable.
• In the end, we have printed the variable to display the new definition. And we have also used sp_executesql to execute the variable as a query.

So, as a result, the query will return the new updated script for the stored procedure. And also execute the same to create a stored procedure with a specified name.

Read: Try catch in SQL Server stored procedure

Stored procedure to rename table in SQL Server

The SQL Server provides one system stored procedure to rename a user-created object in a database. The name of the system stored procedure is sp_rename and it has the following syntax.

EXEC sp_rename 'old_table_name', 'new_table_name'  

Let’s understand its usability with the help of an example. And for the example demonstration, we are using dbo.Students table and has the following data.

Now, we will try to rename this Students table to Student_info using the sp_rename stored procedure. And query for this task is illustrated below.

USE [sqlserverguides]
GO

EXEC sp_rename 'Students', 'Student_info' 

The execution of the above example will rename the Students table to Student_info. However, SQL Server will also return one warning while execution.

And now, while querying the table, we can use the new name of the table.

You may also like to read the following SQL Server tutorials.

• Stored procedure for search functionality in SQL Server
• SQL Server stored procedure modified date
• Drop stored procedure SQL Server
• How to test stored procedure in SQL Server
• SQL Server stored procedure case statement
• SQL Server find text in stored procedure
• Could not find stored procedure in SQL Server
• SQL Server stored procedure parameters
• SQL Server stored procedure return value

So, in the tutorial, we have learned how to rename stored procedure in SQL Server. And we also covered different ways to rename a stored procedure with examples. The list of topics that we have covered is given below.

• Rename stored procedure in SQL Server
• Rename stored procedure in SQL Server using SQL Server Management Studio
• Rename stored procedure in SQL Server using query
• Copy and rename a stored procedure in SQL Server
• Stored procedure to rename table in SQL Server