Sometimes it is necessary to delete all stored procedures from a database. But not delete system stored procedures and functions. The following two SQL statements accomplish just that.
-- -----------------------------------------------------------------------------
-- Drop all stored procedures not shipped with MS SQL Server.
-- -----------------------------------------------------------------------------
PRINT 'Delete all stored procedures not shipped with MS SQL Server.'
DECLARE @prc varchar(255)
WHILE (
SELECT Count(*) from sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
) > 0
BEGIN
SET @prc = (
SELECT TOP 1 p.Name from sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
)
SET @prc = 'DROP PROCEDURE ' + @prc
PRINT @prc
EXEC(@prc)
END