How to delete multiple Stored Procedures in One Database

Question

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'.

Answer 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.