Skip to content

Latest commit

 

History

History
82 lines (58 loc) · 4.14 KB

delete-a-stored-procedure.md

File metadata and controls

82 lines (58 loc) · 4.14 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
Delete a Stored Procedure
Learn how to delete a stored procedure in SQL Server by using SQL Server Management Studio or Transact-SQL.
WilliamDAssafMSFT
wiassaf
randolphwest
09/28/2022
sql
stored-procedures
how-to
ignite-2024
removing stored procedures
stored procedures [SQL Server], deleting
deleting stored procedures
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Delete a stored procedure

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]

This article describes how to delete a stored procedure in [!INCLUDE ssnoversion-md] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

Limitations and restrictions

Deleting a procedure can cause dependent objects and scripts to fail when the objects and scripts are not updated to reflect the removal of the procedure. However, if a new procedure of the same name and the same parameters is created to replace the one that was deleted, other objects that reference it will still process successfully. For more information, see View the Dependencies of a Stored Procedure.

Permissions

Requires ALTER permission on the schema to which the procedure belongs, or CONTROL permission on the procedure.

Use SQL Server Management Studio

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE] and then expand that instance.

  2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

  3. Expand Stored Procedures, right-click the procedure to remove, and then select Delete.

  4. To view objects that depend on the procedure, select Show Dependencies.

  5. Confirm the correct procedure is selected, and then select OK.

  6. Remove references to the procedure from any dependent objects and scripts.

Use Transact-SQL

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE] and then expand that instance.

  2. Expand Databases, expand the database in which the procedure belongs, or, from the tool bar, select the database from the list of available databases.

  3. On the File menu, select New Query.

  4. Obtain the name of stored procedure to remove in the current database. From Object Explorer, expand Programmability and then expand Stored Procedures. Alternatively, in the query editor, run the following statement.

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

  5. Copy and paste the following example into the query editor and insert a stored procedure name to delete from the current database.

    DROP PROCEDURE [<stored procedure name>];
GO

  6. Remove references to the procedure from any dependent objects and scripts.

See also