SQL Query to Rename Stored Procedure

Stored Procedure is a saved SQL code. It is created to save time as we can use it again and again without writing the whole query. In this article, we will see how to Rename a Stored Procedure. 

 To create a Procedure:

Syntax:

CREATE PROCEDURE procedure_name AS 
SELECT * FROM table_name;

SQL query whose procedure needs to be created is always written after the AS statement.

To execute procedure:

Syntax:

EXEC procedure_name;

We can perform the above action by two methods. Those methods are:

• By using sp_rename statement in SQL Server.
• From Object Explorer in SQL Server Management Studio(SSMS).

For demonstration follow the below steps:

Step 1: Create a database

The database can be created using CREATE command.

Query:

CREATE DATABASE geeks;

Step 2: Using a database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3:  Table definition

We have the following demo_table in our geek’s database.

Query:

CREATE TABLE demo_table(
NAME VARCHAR(20),
AGE INT,
CITY VARCHAR(20) );

Step 4: Insert data into a table

Query:

INSERT INTO demo_table VALUES
('ROMY KUMARI', 22, 'NEW DELHI'),
('RINKLE ARORA',23, 'PUNJAB'),
('AKANKSHA GUPTA',22, 'PUNJAB'),
('SUJATA JHA', 30,'PATNA'),
('PREETI GIRI', 26,'BANGLORE'),
('PREM GUPTA',31,'PUNE');

Output:

Step 5: Create procedure

We will create a procedure called ‘Display‘ to view all the content of the table.

Query:

CREATE PROCEDURE Display AS  
SELECT * FROM demo_table;

Output:

Step 6: Rename Procedure

Method 1: using sp_rename

Syntax:

EXEC sp_rename 'Current procedure_name', 'New procedure_name';

For demonstration let’s rename the procedure name from ‘Display’ to ‘Show’.

Query:

EXEC sp_rename 'Display', 'Show';

Execute:

EXEC Show;

Output:

Method 2: From Object Explorer

Step 1: Open object Explorer from View option in the menu bar. It appears on the left side of the screen.

We can see the ‘geeks’ database inside the database folder.

Step 2: Expand the Programmability folder inside the geek’s database. Then expand the Stored procedure folder

Step 3: Right-click on the procedure created and select the ‘Rename’ option.

Step 4: Type the name with which you want to rename the procedure

In these two ways, we can rename the stored procedure.