In this article I’ll try to explain how you can create a stored procedure that deletes all other stored procedures from a database in Microsoft SQL Server.

If you filter its selection with a where clause and select only those records that that have type = ‘P’ (this is for procedures) then you can get the names of all the procedures. You can store the list of the procedure names in a temporary table and loop delete the procedures or you can use a cursor. I’ve used a cursor and deleted the procedures one by one using the EXEC function.

Here is the code.

Alter 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

Go

      Grant Execute On dbo.DeleteAllProcedures To Public

Go

Be very careful when calling this stored procedure. It’s really useful for special purposes and not for every day use.

• 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