SQL 一次性删除所有存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14424647/
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
Delete all Stored Procedures at once
提问by Mahdi Tahsildari
I have 200 Stored Proceduresin my Sql server 2008 R2database that are automatically generated by an application. Now I want to delete them alland regenerate them because the tables have changed a lot.
Stored Procedures我的Sql server 2008 R2数据库中有 200 个是由应用程序自动生成的。现在我想将它们全部删除并重新生成它们，因为表已经改变了很多。
This question is very similar to my casebut in my case all the SP's start with sp_and I thinks it's dangerous to use the same code since system SP's also start with sp_and I may kill them all.
这个问题与我的情况非常相似，但在我的情况下，所有 SP 都以sp_ 开头，我认为使用相同的代码很危险，因为系统 SP 也以sp_ 开头，我可能会将它们全部杀死。
Should I trust the solution in the link above? If not is there any safer solution?
我应该相信上面链接中的解决方案吗？如果没有，有没有更安全的解决方案？
回答by Aaron Bertrand
If this is a one- time task, just open Object Explorer, expand your database > programmability and highlight the Stored Procedures node. Then turn on Object Explorer Details (F7 I think). On the right you should see your list, and here you can multi-select - so you can sort by name, choose all procedures that start with sp_, and delete them all with one keystroke.
如果这是一次性任务，只需打开对象资源管理器，展开数据库 > 可编程性并突出显示存储过程节点。然后打开对象资源管理器详细信息（我认为是 F7）。在右侧，您应该会看到您的列表，在这里您可以进行多选——这样您就可以按名称排序，选择所有以 sp_ 开头的程序，然后一键删除它们。
If you are doing this repeatedly, then (assuming your procedures are all in the dbo schema):
如果您重复执行此操作，则（假设您的过程都在 dbo 架构中）：
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'DROP PROCEDURE dbo.'
+ QUOTENAME(name) + ';
' FROM sys.procedures
WHERE name LIKE N'sp[_]%'
AND SCHEMA_NAME(schema_id) = N'dbo';
EXEC sp_executesql @sql;
回答by Bhavsar Jay
-- drop all user defined stored procedures
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 spajce
I found this How to Drop All Stored Procedures in Your Databasethen I tested and the StoredProcedureis created at outside of Stored Proceduresfolder.
我找到了这个How to Drop All Stored Procedures in Your Database然后我测试了它StoredProcedure是在Stored Procedures文件夹之外创建的。
CREATE PROC UserStoredProcedure_Sample1
AS
SELECT 'SQL Server rocks'
GO
CREATE PROC UserStoredProcedure_Sample2
AS
SELECT 'SQL Server rocks'
GO
SET NOCOUNT ON
-- to do this we have to use EXEC instead of sp_executesql
-- sp_executesql does not accept a DROP command in the SQL String
DECLARE @UserStoredProcedure VARCHAR(100)
DECLARE @Command VARCHAR(100)
DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR
SELECT
SPECIFIC_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
OPEN UserStoredProcedureCursor
FETCH NEXT FROM UserStoredProcedureCursor
INTO @UserStoredProcedure
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure
-- display; visual check
SELECT @Command
-- when you are ready to execute, uncomment below
EXEC (@Command)
FETCH NEXT FROM UserStoredProcedureCursor
INTO @UserStoredProcedure
END
CLOSE UserStoredProcedureCursor
DEALLOCATE UserStoredProcedureCursor
SET NOCOUNT OFF
回答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
回答by Mandani Navneet
DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects
WHERE type = 'p' AND is_ms_shipped = 0
exec(@sql);
