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


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


SET @sqlStr = 'drop procedure ' + @spname

--Execute the drop Statement

EXEC @sqlStr

--Start Fetching other stored procedure Name

fetch next from spDropCursor1 into @spname


--Clean Up

close spDropCursor1

deallocate spDropCursor1



Author: Alwyn Duraisingh23 Oct 2009 Member Level: Gold   Points : 0

Instead of using cursor, if u implement looping it would be more efficient.

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: