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_.
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'.
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.
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.
SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']' as column into #tmptable
FROM sys.procedures p WHERE p.name like 'spV400%'
ORDER BY p.name
declare @deleteAll nvarchar(max)
select @deleteAll = coalesce(@deleteAll + '; ', + '') + #tmptable.column from #tmptable
EXEC sp_executesql @deleteAll
drop table #tmptable
This will insert all the stored procedure names in the temp table and then convert them in semicolon separated value and drop all stored procedures.
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.
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.
1 Click on Stored Procedures Tab
2 Filter Store Procedures Using Filter Functionality of SQL
3 Select All Procedure By Ctrl + A except System Table
4 Press Delete button and Click OK.
or Find the link http://www.devasp.net/net/articles/display/309.html
