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

Question

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?

Answer 1

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 [' + SCHEMA_NAME(p.schema_id) + '].[' + 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