在 SQL Server 数据库中如何一次性删除所有存储过程？

Question

目前我们在脚本文件中为每个存储过程使用单独的DROP语句：

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]')   
  AND type in (N'P', N'PC'))  
DROP PROCEDURE [dbo].[MySP] 

有没有一种方法可以一次性删除它们，或者可能在循环中删除？

- z-boss

Answer 1

我更倾向于以下方式：

• 首先，通过检查系统目录视图来生成要删除的存储过程列表：

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];'
FROM sys.procedures p 

这会在你的SSMS输出窗口中生成一个DROP PROCEDURE语句列表。

• 将该列表复制到新的查询窗口中，可能需要适当修改/更改后再执行

没有混乱和缓慢的光标，使你能够在实际删除之前检查和反复检查要删除的过程列表。

- marc_s

---

我需要这个用于部署，所以它必须是自动的。 - z-boss

我必须承认，使用光标比复制和粘贴快多了。 - satnhak

非常出色的答案，节省了我大量的时间。 - Abbas

这个工具非常有用，特别是当你不小心在某个地方添加了 SP（比如主数据库）时。它提供了一种简单的方式来进行预检和清理。 - MytyMyky

更喜欢这种方式！如果你只想删除一些、一半或其中的某个子集，这种方式也非常方便。如果你加上一个“order by”，就可以对其进行排序，在删除之前更好地查看它们。 - Gomibushi

Answer 2

类似以下内容 (取自使用SQL Server存储过程从数据库中删除所有过程)。

顺便说一下，这似乎是一个非常危险的操作，只是想提醒一下...

declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure [' + @procName + ']')
    fetch next from cur into @procName
end
close cur
deallocate cur

- Adriaan Stander

---

首先，因为它缺少一个额外的WHERE子句：对于选择[name]从sys.objects中，其中type = 'p'并且is_ms_shipped = 0。 - dividius

非常好用，当我不小心将几百个存储过程写入主数据库而不是特定数据库时，我会使用它。 - Brent

顺便提一下，如果您有任何奇怪的存储过程名称，请在名称周围添加 [ ]。执行语句应该像这样： exec('drop procedure [' + @procName + ']') - Brad Irby

无畏的开发者喜欢危险的脚本。Namaste。 - Mariusz

此脚本缺少模式名称，仅适用于 dbo 对象。 - Lemiarty

Answer 3

1. 在对象资源管理器窗格中，选择存储过程文件夹。
2. 按 F7 键（或从主菜单中选择“查看” > “对象资源管理器详细信息”）。
3. 选择所有的过程除了系统表。
4. 按删除键并选择确定。

您可以以同样的方式删除表格或视图。

- Sandip

Answer 4

在您的数据库中创建以下存储过程（从您想要删除存储过程的数据库中）

然后右键单击该存储过程 - 单击“执行存储过程”..

然后点击确定。

create Procedure [dbo].[DeleteAllProcedures]
As 
declare @schemaName varchar(500)    
declare @procName varchar(500)
declare cur cursor
for select s.Name, p.Name from sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
ORDER BY s.Name, p.Name
open cur

fetch next from cur into @schemaName,@procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @schemaName + '.' + @procName)
fetch next from cur into @schemaName,@procName
end
close cur
deallocate cur

- Rizwana

---

最佳答案！也处理模式。 - uzul

Answer 5

我认为这是最简单的方法：

DECLARE @sql VARCHAR(MAX)='';

SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects 
WHERE type = 'p' AND  is_ms_shipped = 0

exec(@sql);

- Tony O'Hagan

Answer 6

获取数据库中所有存储过程的删除语句
SELECT 'DROP PROCEDURE' + ' ' + F.NAME + ';' FROM SYS.objects AS F where type='P'

- user7050445

Answer 7

尝试一下，这对我有效。

DECLARE @spname sysname;
DECLARE SPCursor CURSOR FOR
SELECT SCHEMA_NAME(schema_id) + '.' + name
FROM sys.objects
WHERE type = 'P';
OPEN SPCursor;
FETCH NEXT FROM SPCursor INTO @spname;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP PROCEDURE ' + @spname);
FETCH NEXT FROM SPCursor INTO @spname;
END
CLOSE SPCursor;
DEALLOCATE SPCursor;

- Aymen Bouein

Answer 8

DECLARE @sql VARCHAR(MAX)
SET @sql=''
SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects
WHERE type = 'p' AND  is_ms_shipped = 0
exec(@sql);

- Mrityunjay Ravi

---

仅提供代码答案是不被赞赏的。请附上您的代码并加入少许解释。 - Sulthan Allaudeen

Answer 9

符合ANSI标准，无光标

PRINT ('1.a. Delete stored procedures ' + CONVERT( VARCHAR(19), GETDATE(), 121));
GO
DECLARE @procedure NVARCHAR(max)
DECLARE @n CHAR(1)
SET @n = CHAR(10)
SELECT @procedure = isnull( @procedure + @n, '' ) +
'DROP PROCEDURE [' + schema_name(schema_id) + '].[' + name + ']'
FROM sys.procedures

EXEC sp_executesql @procedure
PRINT ('1.b. Stored procedures deleted ' + CONVERT( VARCHAR(19), GETDATE(), 121));
GO

- jorgedison

Answer 10

通过混合使用光标和系统过程，我们可以得到一个优化的解决方案，具体如下：

DECLARE DelAllProcedures CURSOR
FOR
    SELECT name AS procedure_name 
    FROM sys.procedures;
OPEN DelAllProcedures
DECLARE @ProcName VARCHAR(100)
FETCH NEXT 
FROM DelAllProcedures
INTO @ProcName
WHILE @@FETCH_STATUS!=-1
BEGIN 
    DECLARE @command VARCHAR(100)
    SET @command=''
    SET @command=@command+'DROP PROCEDURE '+@ProcName
    --DROP PROCEDURE  @ProcName
    EXECUTE (@command)
    FETCH NEXT 
    FROM DelAllProcedures
    INTO @ProcName
END
CLOSE DelAllProcedures
DEALLOCATE DelAllProcedures

- Mohamad Reza Taghipour