如何一次删除 SQL Server 数据库中的所有存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2610820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop all stored procedures at once in SQL Server database?
提问by z-boss
Currently we use separate a drop statements for each stored procedure in the script file:
目前我们为脚本文件中的每个存储过程使用单独的 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]
Is there a way to drop them all at once, or maybe in a loop?
有没有办法一次性删除它们，或者循环删除它们？
回答by marc_s
I would prefer to do it this way:
我更愿意这样做：
first generate the list of stored procedures to drop by inspecting the system catalog view:
SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];' FROM sys.procedures pThis generates a list of
DROP PROCEDUREstatements in your SSMS output window.copy that list into a new query window, and possibly adapt it / change it and then execute it
首先通过检查系统目录视图生成要删除的存储过程列表：
SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];' FROM sys.procedures p这会
DROP PROCEDURE在您的 SSMS 输出窗口中生成一个语句列表。将该列表复制到新的查询窗口中，并可能对其进行调整/更改，然后执行它
No messy and slow cursors, gives you the ability to check and double-check your list of procedure to be dropped before you actually drop it
没有杂乱和缓慢的游标，使您能够在实际删除之前检查并仔细检查要删除的过程列表
回答by Adriaan Stander
Something like (Found at Delete All Procedures from a database using a Stored procedure in SQL Server).
类似于（在使用 SQL Server 中的存储过程从数据库中删除所有过程中找到）。
Just so by the way, this seems like a VERYdangerous thing to do, just a thought...
顺便说一句，这似乎是一件非常危险的事情，只是一个想法......
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
回答by Sandip
- Click on Stored Procedures Tab
- Press f7 to Display All Stored Procedures
- Select All Procedure By Ctrl + A except System Table
- Press Delete button and Click OK.
- 单击存储过程选项卡
- 按 f7 显示所有存储过程
- 通过 Ctrl + A 选择除系统表之外的所有过程
- 按删除按钮，然后单击确定。
You can Delete Table as well as View in same manner.
您可以以相同的方式删除表和视图。
回答by Rizwana
create below stored procedure in your db(from which db u want to delete sp's)
在您的数据库中创建以下存储过程（您要从中删除 sp 的数据库）
then right click on that procedure - click on Execute Stored Procedure..
然后右键单击该过程 - 单击执行存储过程..
then click ok.
然后点击确定。
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
回答by user7050445
To get drop statements for all stored procedures in a database SELECT 'DROP PROCEDURE' + ' ' + F.NAME + ';' FROM SYS.objects AS F where type='P'
获取数据库中所有存储过程的 drop 语句 SELECT 'DROP PROCEDURE' + ' ' + F.NAME + ';' FROM SYS.objects AS F where type='P'
回答by Aymen Bouein
Try this, it work for me
试试这个，它对我有用
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;
回答by Mrityunjay Ravi
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);
回答by Tony O'Hagan
I think this is the simplest way:
我认为这是最简单的方法：
DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects
WHERE type = 'p' AND is_ms_shipped = 0
exec(@sql);
回答by Mons'
Try this:
尝试这个：
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'DROP PROCEDURE dbo.'
+ QUOTENAME(name) + ';
' FROM sys.procedures
WHERE name LIKE N'spname%'
AND SCHEMA_NAME(schema_id) = N'dbo';
EXEC sp_executesql @sql;
回答by Falcon
DECLARE @DeleteProcCommand NVARCHAR(500)
DECLARE Syntax_Cursor CURSOR
FOR
SELECT 'DROP PROCEDURE ' + p.NAME
FROM sys.procedures p
OPEN Syntax_Cursor
FETCH NEXT FROM Syntax_Cursor
INTO @DeleteProcCommand
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC (@DeleteProcCommand)
FETCH NEXT FROM Syntax_Cursor
INTO @DeleteProcCommand
END
CLOSE Syntax_Cursor
DEALLOCATE Syntax_Cursor
