MSDotNetBuddy

Sometime we quickly want to drop all stored procedure as there are many stored procedure in db we can't go one by one on delete process.
Other way round is keep a stored procedure in developmental database that can do this task for us.When we create a release version of our database make sure that you remove it else someone play havoc.

The procedure can be written as follow

Stored Procedure Code listed bellow:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create Procedure [dbo].[DeleteAllProcedures]
As
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = 'p'
open cur

fetch next from cur into @procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur

The code above will delete all stored procedure except itself
To See it in action run Query Bellow

EXEC DeleteAllProcedures

Try Yourself But Make sure you doesn't delete something that is of use before backup.