How to Delete Stored Procedures in SQL Server

As a database developer, you must know how to delete stored procedures in SQL Server when they are no longer needed.

In this SQL Server tutorial, I will explain two methods for removing stored procedures from the database.

In the first method, you will use the SSMS to delete the stored procedures; in the second, you will use the query.

Let’s start,

Table of Contents

Delete Stored Procedures in SQL Server

The command DELETE PROCEDURE can be used to delete stored procedures in SQL Server. You can also use the SQL Server Management Studio (SSMS) to remove the stored procedure in your database.

But why remove the stored procedure? Due to changes in the business logic, when you don’t need the stored procedure or replace it with new logic, you remove the stored procedure.

Let’s start,

Delete Stored Procedures in the SQL Server using SSMS

You delete stored procedures in SQL Server using the SQL Server Management Studio, a graphical user interface for managing SQL Server Database.

First, let me show you how to view all the stored procedures in your current database.

Open the SSMS, go to the left side of the Object Explorer, expand the Database folder, choose the database that contains the stored procedure, expand the Programmability subfolder, and finally expand the Stored Procedures subfolder, as shown below.

Viewing Procedures Before to Delete Stored Procedures in the SQL Server using SSMS

The subfolder Stored Procedures contains all the system—and user-created stored procedures; for example, store procedure dbo.uspCounter.

To delete this stored procedure, select and right-click on it, then select the Delete option, as shown in the below picture.

Selecting Delete Option to Delete Stored Procedures in the SQL Server using SSMS

When you click the Delete option, it opens the Delete Object dialogue below.

Delete Stored Procedures in the SQL Server using SSMS

To completely delete it, click the OK button at the bottom right of the dialogue box, as shown in the above output.

When you reopen the Stored Procedures subfolder, you won’t see the deleted stored procedure. In the picture below, the dbo.uspCounter is removed.

Viewing Deleted Stored Procedures in the SQL Server using SSMS

You don’t see any stored procedures with the name ‘dbo.uspCounter’ in the list because you deleted it.

This is how to Delete Stored Procedures in the SQL Server using SSMS.

Delete Stored Procedures in the SQL Server using Query

You just learned how to delete the stored procedure using the SSMS; here, you will learn how to remove the stored procedure using the command DROP PROCEDURE in SQL Server.

The syntax is given below.

DROP PROCEDURE name_of_procedure;

Where,

DROP PROCEDURE: It is the command to delete the specified stored procedure.
name_of_procedure: This represents the name of the stored procedure that you want to delete from the database.

First, execute the query below to list all the procedures stored in the current database.

SELECT name AS procedure_name
    , SCHEMA_NAME(schema_id) AS schema_name
    , type_desc
    , create_date
    , modify_date
FROM sys.procedures;

Listing Stored Procedure using the Query to Delete

Let’s say you must delete the usp_GetEmployees stored procedure from the listed stored procedure. For that, you can use the DROP PROCEDURE command, as shown below.

DROP PROCEDURE usp_GetEmployees;

When you execute the above query, the specified stored procedure is deleted. So again, execute the below query to view all the procedures stored in the current database.

SELECT name AS procedure_name FROM sys.procedures;

Delete Stored Procedures in the SQL Server using Query

From the output, you can see the stored procedure usp_GetEmployees doesn’t exist anymore cause it is deleted.

This is how to delete stored procedures in SQL Server using the query.

Some practices that you must know before deleting any stored procedure.
Always have a backup of the stored procedures before deletion.
Ensure that the stored procedure you delete does not rely on other database objects, like triggers or other stored procedures.
Use the transaction while deleting the stored procedures to prevent any partial changes in case any error occurs.
Always keep track of the stored procedure you deleted and why you deleted it for future reference.

Conclusion

In this SQL Server tutorial, you learned how to delete stored procedures in SQL Server using the SSMS and query.

You learned step-by-step processes for removing stored procedures using SQL Server Management. Then, you used the DROP PROCEDURE command as a query to delete the stored procedure.