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
4
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
0
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
0
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
0
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
4
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
0
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
0
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
0
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.
复制结果并执行。这是所有。