SQL: Script to drop all stored procedures from the database

The following SQl stored procedure dropAllSpproc is used to drop all stored procedures from the database.

We need to mention the schema name in the stored procedure.

/*The procedure declares the cursor to iterate through all SP List*/

/*This procedure retrieves SP Name from sysobjects based on type 'p'

/*The type 'p' indicates - it is a stored procedure */

CREATE PROCEDURE [dbo].[dropAllSpProc] AS



     --Declaration of Cursor type

DECLARE spDropCursor1 cursor for




     --Retrieve the names of Stored Procedures




SELECT name

FROM sysobjects

WHERE type = 'P' -- 'P' indicates Stored Procedure



     --Open the spDropCursor

OPEN spDropCursor1



     --Declaraion of Variable to store Stored procedure name



DECLARE @spName varchar(100)

DECLARE @sqlStr varchar(100)


  
   --Fetch the first name into SPName variable


FETCH NEXT FROM spDropCursor1 INTO @spname



  --Start the Loop

WHILE @@fetch_status = 0


begin


SET @sqlStr = 'drop procedure ' + @spname 


   --Execute the drop Statement


EXEC @sqlStr



   --Start Fetching other stored procedure Name

fetch next from spDropCursor1 into @spname



end



   --Clean Up

close spDropCursor1



deallocate spDropCursor1



Go