Deleting Stored Procedures in SQL Server

As applications evolve, some stored procedures become obsolete or must be removed to simplify maintenance and reduce technical debt. In SQL Server, deleting a user-defined stored procedure is a straightforward task, but it should be performed carefully because the action is permanent.

This lesson explains the supported methods for deleting stored procedures and highlights important considerations such as dependencies and permissions.

Methods for Deleting Stored Procedures

There are two common ways to delete a user-defined stored procedure:

1. Using SQL Server Management Studio (SSMS) — a graphical approach.
2. Using Transact-SQL (T-SQL) — the recommended and scriptable approach.

Deleting a Stored Procedure with SSMS

To delete a stored procedure using SSMS:

1. Connect to the SQL Server instance in Object Explorer.
2. Expand the target database.
3. Expand Programmability → Stored Procedures.
4. Right-click the stored procedure you want to delete.
5. Select Delete, then confirm the action.

SSMS provides a convenient interface, but it ultimately executes a DROP PROCEDURE statement behind the scenes.

Deleting a Stored Procedure Using Transact-SQL

The preferred and most precise method is to use T-SQL. The basic syntax is shown below:

DROP PROCEDURE procedure_name;

You can delete multiple stored procedures in a single statement by listing them separated by commas:

DROP PROCEDURE procedure_one, procedure_two;

Example

To delete a stored procedure named usp_DeleteEmployee, execute the following:

DROP PROCEDURE dbo.usp_DeleteEmployee;

Schema qualification (such as dbo) is recommended to ensure the correct procedure is removed.

Important Considerations

• Irreversible action: Once dropped, a stored procedure cannot be recovered unless it is recreated from a backup or script.
• Dependencies: Other database objects (procedures, views, or applications) may depend on the stored procedure. Review dependencies before deletion to avoid runtime errors.
• Permissions: Dropping a procedure permanently removes all permissions associated with it.
• User-defined only: The DROP PROCEDURE statement applies to user-defined stored procedures, not system or extended stored procedures.

The IF EXISTS clause (SQL Server 2016+):

sql   DROP PROCEDURE IF EXISTS dbo.usp_DeleteEmployee;

In the next lesson, you will review how stored procedures relate to other database objects and how to manage them safely in production environments.