删除存储过程Delete a Stored Procedure

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题介绍了如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中删除存储过程。This topic describes how to delete a stored procedure in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

开始之前Before You Begin

限制和局限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.

SecuritySecurity

权限Permissions

需要拥有对该过程所属架构的 ALTER 权限,或对该过程的 CONTROL 权限。Requires ALTER permission on the schema to which the procedure belongs, or CONTROL permission on the procedure.

如何删除存储过程How to Delete a Stored Procedure

您可以使用以下项之一:You can use one of the following:

使用 SQL Server Management StudioUsing SQL Server Management Studio

在对象资源管理器中删除过程To delete a procedure in Object Explorer

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine 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 click Delete.

  4. 若要查看依赖于过程的对象,请单击 “显示依赖关系”To view objects that depend on the procedure, click Show Dependencies.

  5. 确认选择了正确的过程,再单击 “确定”Confirm the correct procedure is selected, and then click OK.

  6. 从所有依赖对象和脚本中删除对该过程的引用。Remove references to the procedure from any dependent objects and scripts.

期待您的反馈 :如果在本文中发现过时或不正确的内容(如步骤或代码示例),请告诉我们。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 可以单击此页底部的“反馈” 部分中的“本页” 按钮。You can click the This page button in the Feedback section at the bottom of this page. 我们通常在第二天阅读有关 SQL 的每项反馈。We read every item of feedback about SQL, typically the next day. 谢谢。Thanks.

使用 Transact-SQLUsing Transact-SQL

在查询编辑器中删除过程To delete a procedure in Query Editor

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine 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, click 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

创建存储过程 Create a Stored Procedure
修改存储过程 Modify a Stored Procedure
重命名存储过程 Rename a Stored Procedure
查看存储过程的定义 View the Definition of a Stored Procedure
查看存储过程的依赖关系 View the Dependencies of a Stored Procedure
DROP PROCEDURE (Transact-SQL)DROP PROCEDURE (Transact-SQL)