Drop stored procedure SQL Server

In this SQL Server tutorial, we will discuss how to drop a stored procedure in SQL Server. I will explain what are the various methods to drop stored procedures with the help of some examples.

• How to drop stored procedure SQL Server?
• Delete stored procedure SQL Server management studio
• Drop stored procedure SQL Server query
• How to drop stored procedure in SQL Server if exists?
• Drop and recreate stored procedure SQL Server
• How to delete multiple stored procedure in SQL Server?
• Drop all stored procedures in SQL Server database
• Drop system stored procedure SQL Server
• Recover dropped stored procedure SQL Server

Prerequisite for this tutorial: What is a stored procedure in sql server

How to drop stored procedure SQL Server?

If you want to delete a stored procedure in SQL Server, you have to take care of a few things.

• Firstly, you must have enough permissions to drop the stored procedure. You must have the ALTER permissions on the schema, or CONTROL permissions on the stored procedure.
• Secondly, you should verify that there should be no object dependent on it. For example, if some scripts are dependent on a stored procedure that you are deleting, the script may fail executing after the stored procedure is dropped.

There are two methods that you can use to drop a stored procedure in SQL Server.

1. Using SQL Server management studio
2. Using T-SQL query

Let us see how you can use these methods.

Delete stored procedure SQL Server management studio

If you are using SQL Server management studio to connect to your database, you can easily drop a stored procedure because of its GUI. In SQL Server management studio, you can also view the stored procedure dependencies before deleting the stored procedure.

Let us see how to do that.

• Open the object explorer window and navigate to Programmability > Stored Procedures under your database.
• Here you can see the list of stored procedures that are created in the database.
• Right click on the stored procedure that you want to delete and click on Delete.

• Then you will see a Delete Object window. Here you can click on Show Dependencies… to know the dependencies of your stored procedure i.e. the objects on which the stored procedure is dependent and the objects that are dependent on the stored procedure.
• Once verified, click on OK to delete the stored procedure.

Hence, in this way, you can drop a stored procedure using SQL Server management studio.

Drop stored procedure SQL Server query

To drop a stored procedure using a T-SQL query, you can use the DROP PROCEDURE statement. The general syntax of the DROP PROCEDURE statement is:

DROP PROCEDURE <procedure name>

For example, I have created a procedure named dbo.Addition in a database named BackupDatabase. If I want to drop this procedure, I will write the DROP PROCEDURE statement as:

USE BackupDatabase
GO
DROP PROCEDURE dbo.Addition

Also, check: How to execute stored procedure in SQL Server

How to drop stored procedure in SQL Server if exists?

You can also use the argument IF EXISTS while using the DROP PROCEDURE statement. This helps to avoid the error if you are trying to drop a procedure that does not exist.

This is because the DROP PROCEDURE statement will drop the stored procedure only if it exists in the database.

The general syntax for using the IF EXISTS argument with the DROP PROCEDURE statement is:

DROP PROCEDURE IF EXISTS <stored procedure name>

For example, If I try to drop the stored procedure named dbo.Addition again without using the IF EXISTS argument, I will face an error as shown below in the image:

Now let us use the IF EXISTS argument with the DROP PROCEDURE statement:

USE BackupDatabase
GO
DROP PROCEDURE IF EXISTS dbo.Addition

You can see that this time we did not get any error. Also, if you will specify a stored procedure that exists in the database, the procedure will be dropped.

Hence, you might have learned how IF EXISTS work with the DROP PROCEDURE statement.

Read: SQL Server stored procedure if exists update else insert

Drop and recreate stored procedure SQL Server

To drop and recreate a stored procedure, you will first check if the stored procedure exists in the database or not. If it exists, you can drop it using the DROP PROCEDURE IF EXISTS statement and create it again with the CREATE PROCEDURE statement.

The general syntax will be:

DROP PROCEDURE IF EXISTS <procedure name>
GO
CREATE PROCEDURE <procedure name> <parameters list>
AS
BEGIN
<stored procedure body>
END

For example, I have created a stored procedure named dbo.Addition. I will drop this stored procedure and create it again with a new body.

USE BackupDatabase
GO
DROP PROCEDURE IF EXISTS dbo.Addition
GO
CREATE PROCEDURE dbo.Addition @num1 int, @num2 int
AS
BEGIN
	DECLARE
	@Sum int
	SET @Sum = @num1 + @num2
	PRINT('Sum is:' + STR(@Sum))
END

Now let us execute this stored procedure and see if it works in the desired way.

USE BackupDatabase
GO
EXEC dbo.Addition 34, 53

You can see that the stored procedure returned the result as we expected.

Hence, in this way, you can drop a procedure and create it again.

Read: SQL Server find text in stored procedure

How to delete multiple stored procedure in SQL Server?

To delete multiple stored procedures using a single DROP PROCEDURE statement, you can specify the names of the stored procedures separated by a comma. For example,

DROP PROCEDURE <procedure 1>, <procedure 2>, <procedure 3>

I have some stored procedures created in my database. Let us try to drop 3 stored procedures at once. I will use the below T-SQL query:

USE [BackupDatabase]
GO
DROP PROCEDURE dbo.GetData, dbo.multiply, dbo.SelectData
GO

After executing the above statement, all the three stored procedures were deleted from the database. In this way, you can delete multiple stored procedures using a single query in SQL Server.

Using SQL Server management studio:

You can also use SQL Server management studio to drop multiple stored procedures with ease. Follow the below steps to drop multiple stored procedures:

• In the object explorer window, navigate to Stored Procedures under your database. Click on this folder and press the F7 key on your keyboard.
• A window will be opened where you can see the list of all stored procedures.

• Select the stored procedures that you want to delete by simultaneously pressing the CTRL button + left click.
• Right click on a selected item and click on Delete or simply press the Delete button on your keyboard.
• Verify the dependencies of the stored procedures and click on OK to delete the stored procedures.

Read: Rename stored procedure in SQL Server

Drop all stored procedures in SQL Server database

There might be some scenarios when you create some stored procedures that you were not supposed to. You created multiple stored procedures that caused some conflicts in the scripts in the database.

Similarly, there are other reasons when you needed to drop all the stored procedures from that database. In that case, you can use either of the two ways to drop all the stored procedures in the database:

Using SQL Server management studio:

You can use the process as I explained in the above section i.e. press the F7 key and select all the stored procedures, then delete them. This is the easiest way to drop all the stored procedures.

Using T-SQL scripts:

You can create a T-SQL script that will fetch the names of all the stored procedures in the database. Then you can use a loop or a cursor and use the DROP PROCEDURE statement to delete all the stored procedures.

Let us see some of the scripts that you can use to drop all the stored procedures:

You can execute a script like the below script to delete all the user-defined stored procedures in your database:

USE BackupDatabase
GO
DECLARE @query VARCHAR(MAX)
SET @query=''
SELECT @query = @query + 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];'
FROM sys.procedures p
EXECUTE(@query)
SELECT * FROM sys.procedures

The above script will retrieve the names of all the stored procedures and append these names to the DROP PROCEDURE command, separated by a semicolon.

Then the resulting SQL query is executed using the EXECUTE statement. For example, if there are two stored procedures in the database named dbo.FindSum and dbo.PrintSum, the resulting T-SQL query was:

DROP PROCEDURE dbo.FindSum;DROP PROCEDURE dbo.PrintSum

As a result, both the stored procedures will be deleted. Similarly, if you have N number of stored procedures in the database, there will be N number of DROP PROCEDURE queries stored in the @query variable and all the stored procedures will be dropped.

In this way, you can create your own customized scripts to drop all the stored procedures in a database.

Read: Could not find stored procedure in SQL Server

Drop system stored procedure SQL Server

You cannot drop a system stored procedure in SQL Server 2005 or above versions. Earlier it was possible to drop system stored procedures.

Dropping system stored procedures may cause many scripts to malfunction or fail to run. Dropping any system object is never advisable. Therefore after the SQL Server 2005 versions, changes to system objects are prohibited.

Read: Trigger in SQL Server for Insert and Update

Recover dropped stored procedure SQL Server

You can use the below script to recover the recently dropped stored procedures in SQL Server.

• The below script will check the transaction logs to find the recently deleted stored procedures.
• Also, the script will find the CREATE PROCEDURE statements in the logs of the corresponding stored procedures returned.
• You just have to specify the time interval between which the stored procedures were deleted.

USE BackupDatabase
GO
DECLARE @From DATETIME = '2021-12-23'
DECLARE @To DATETIME = '2021-12-24'

SELECT 
    CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM
    fn_dblog(NULL,NULL)
WHERE 
    [Operation] = 'LOP_DELETE_ROWS' 
    AND [Context] = 'LCX_MARK_AS_GHOST'
    AND [AllocUnitName] = 'sys.sysobjvalues.clst'
    AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] 
                             FROM sys.fn_dblog(NULL, NULL)
                             WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT') 
                               AND [Transaction Name] = 'DROPOBJ'
                               AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @From AND @To)
                               AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0

Also, take a look at some more tutorials on SQL Server.

• Introduction to SQL Server Trigger
• Trigger For Delete SQL Server
• Full-text search in SQL Server
• Alter Stored Procedure in SQL Server
• SQL Server scheduled stored procedure
• How to test stored procedure in SQL Server
• Pass parameters to view in SQL Server

You can see that the stored procedure bodies are returned in the columns. You can copy these columns and create the stored procedure again.

• How to drop stored procedure SQL Server?
• Delete stored procedure SQL Server management studio
• Drop stored procedure SQL Server query
• How to drop stored procedure in SQL Server if exists?
• Drop and recreate stored procedure SQL Server
• How to delete multiple stored procedure in SQL Server?
• Drop all stored procedures in SQL Server database
• Drop system stored procedure SQL Server
• Recover dropped stored procedure SQL Server