How to drop all stored procedures at once in SQL Server database?

Currently we use separate a drop statements for each stored procedure in the script file:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]')   
  AND type in (N'P', N'PC'))  
DROP PROCEDURE [dbo].[MySP] 

Is there a way to drop them all at once, or maybe in a loop?

From stackoverflow

• Something like (Found at Delete All Procedures from a database using a Stored procedure in SQL Server).

  Just so by the way, this seems like a VERY dangerous thing to do, just a thought...

  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
      exec('drop procedure ' + @procName)
      fetch next from cur into @procName
  end
  close cur
  deallocate cur
  

  z-boss : Why do you think it is very dangerous?

• I would prefer to do it this way:

  • first generate the list of stored procedures to drop by inspecting the system catalog view:

    SELECT 'DROP PROCEDURE ' + p.NAME
    FROM sys.procedures p 
    

    This generates a list of DROP PROCEDURE statements in your SSMS output window.

  • copy that list into a new query window, and possibly adapt it / change it and then execute it

  No messy and slow cursors, gives you the ability to check and double-check your list of procedure to be dropped before you actually drop it

  z-boss : I need this for deployment, so it has to be automatic.