Script to delete all stored procedures or procedures with a certain name pattern

Useful script to delete all stored procedures or all procedures with a certain name pattern e.g. all that start with sp etc.  Developed for SQL Server 2008 R2  

2 parts: the first creates a table variable and puts in there the names of the SPs to delete from the information schema. Part 2 cycles through that table variable executing the DROP PROCEDURE command.

Hope that helps, Jon Bosker, DB Gurus, 25 Nov 2015



--------------------------------------------------------------
-- You can set a name pattern of stored procedures to delete:
-- e.g SET @sPattern = 'sp%' to just delete procedures that start with sp
DECLARE @sPattern varchar(MAX)
SET @sPattern = '%'  --<<-- all SPs

DECLARE @t TABLE
(
SPName varchar(8000),
id int identity(1,1)
)

INSERT INTO @t
SELECT SPECIFIC_SCHEMA  + '.[' + SPECIFIC_NAME + ']' FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME LIKE @sPattern


-- SELECT * FROM @t

DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1


WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = 'DROP PROCEDURE ' + SPName  FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)  -- comment out this line to test it first
SET @counter = @counter + 1
END