如何在一个数据库中删除多个存储过程

I am using SQL Server 2008. I have a database Training which contains two types of stored procedures with names prefixed by SP_V400_ and spV400_. Now I need to delete the stored procedures with the name prefix spV400_.

我正在使用SQL Server 2008。我有一个数据库培训，其中包含两种类型的存储过程，名称以SP_V400_和spV400_开头。现在我需要删除带有名称前缀spV400_的存储过程。

I tried this command

我试着这个命令

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '] GO'
FROM sys.procedures p  
WHERE p.name LIKE '%spV400%'
ORDER BY p.name

But I am getting an error:

但我犯了一个错误:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.

Msg 102，第15级，状态1，第1行“GO”语法错误。

4 个解决方案

#1

You don't need the GO at the end.

你不需要最后的努力。

Try this:

试试这个:

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'
FROM sys.procedures p  WHERE p.name like 'spV400%'
ORDER BY p.name

That of course will give you a list of SQL commands in the output which you can copy and paste into SSMS and run.

当然，这将为您提供输出中的SQL命令列表，您可以将这些命令复制并粘贴到SSMS中并运行。

#2

GO is not a SQL cmd, its a windows batch terminator.
So it needs to be in separate line always. All you need to do so is to include char(10) in your code for line feed.

GO不是SQL cmd，而是windows批处理终止符。所以它总是要分开的。您所需要做的就是在代码中包含char(10)作为行提要。

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'+char(10)+'GO'
FROM sys.procedures p  WHERE p.name like '%spV400%'
ORDER BY p.name

Execute the above code and copy paste the resultset in a query window for execution with GO keyword.

执行上述代码，并将resultset复制到查询窗口中，以便使用GO关键字执行。

#3

1 Click on Stored Procedures Tab

单击“存储过程”选项卡。

2 Filter Store Procedures Using Filter Functionality of SQL

使用SQL的过滤功能过滤存储过程

3 Select All Procedure By Ctrl + A except System Table

通过Ctrl + A系统表选择所有程序

4 Press Delete button and Click OK.

4按删除键，点击确定。

or Find the link http://www.devasp.net/net/articles/display/309.html

或者找到链接http://www.devasp.net/articles/display/309.html

#4

This is my approach,

这是我的方法,

select 'DROP PROCEDURE ' + ROUTINE_SCHEMA + '.' + SPECIFIC_NAME
from    YourDBName.information_schema.routines 
where   routine_type = 'PROCEDURE' AND SPECIFIC_NAME like '%test%'

copy the result and execute. that's all.

复制结果并执行。这是所有。

#1