How to drop several stored procedure

_{Wednesday, April 13, 2011 1:52 PM | 1 vote}

Hi,

I got several stored procedure called PSX001.User, PSX002.Address, PSX003.ZIP and more...

I want to be able to drop them all at the same time with a T-SQL command but I can't figure how to do it using some kind of wild card argument like a * or something.

Thanks for helping

_{Wednesday, April 13, 2011 2:01 PM ✅Answered}

There is no way to drop multiple SPs using some pattern with one command. They need to be separate DROP PROCEDURE commands (or one DROP PROCEDURE command listing SPs separated by a comma). Of course, you can automatically generate this command and run as a batch with execute (@SQL) (where @SQL will contain all these commands). For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Wednesday, April 13, 2011 2:00 PM}

The only way to drop multiple stored procedures with one statement is be listing them separated by commas, you cannot use a wildcard.  But you can do

DROP PROCEDURE PSX001.User, PSX002.Address, PSX003.ZIP

and that will drop all three stored procs.

Tom

_{Wednesday, April 13, 2011 2:01 PM | 2 votes}

Try this,

Replace WHERE Condition with your serach condition for wild card.

DECLARE @Cnt INT,
        @DBName Varchar(50),
        @UserName Varchar(50),
        @SQL varchar(MAX)

    SELECT @SQL = ISNULL(@SQL + 'DROP PROCEDURE ', 'DROP PROCEDURE ') + QUOTENAME(s.name) +'.'+ QUOTENAME(p.name) +';'
    FROM sys.procedures p JOIN sys.schemas s 
    ON p.schema_id = s.schema_id
    WHERE type = 'P'
    AND p.name LIKE '%xxx%'
        
PRINT @SQL 
--EXEC(@SQL)

Regards, Deven Please vote if you find any of my post helpful.

_{Wednesday, April 13, 2011 2:01 PM}

You can generate a DROP script with a SELECT statement using meta data:

SELECT 'DROP PROC '+SCHEMA_NAME(schema_id)+'.' + name +';'
FROM sys.procedures
WHERE TYPE='P'
AND name like '%e%'

You can also apply dynamic SQL: http://sqlusa.com/bestpractices/dynamicsql/

Kalman Toth, SQL Server & BI Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

_{Wednesday, April 13, 2011 10:08 PM | 1 vote}

> There is no way to drop multiple SPs using some pattern with one command.

Actually there is!

But only under one certain peculiar circumstance:

CREATE PROCEDURE alfons AS PRINT 1
go
CREATE PROCEDURE alfons;2 AS PRINT 2
go
CREATE PROCEDURE alfons;3 AS PRINT 3
go
EXEC alfons;1
EXEC alfons;2
EXEC alfons;3
go
DROP PROCEDURE alfons
go
SELECT * FROM sys.objects WHERE name like 'alfons%'
go

This is a highly deprecated feature, so don't use it for real!  

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Saturday, August 10, 2013 3:07 PM | 3 votes}

simply

1. Click on Stored Procedures Tab
2. Press f7 to Display All Stored Procedures
3. Select All Procedure By Ctrl + A except System Table
4. Press Delete button and Click OK.

http://stackoverflow.com/questions/2610820/how-to-drop-all-stored-procedures-at-once-in-sql-server-database