SQL Query to delete all stored procedures in SQL Server

Description:

In this example we explain that how to delete all stored procedure in SQL Server.or remove all the stored procedure from SQL Server.SQL Query to remove all the stored procedure from SQL Server.below is the SQL Query to remove or delete or drop the all the stored procedure in SQL Server.

Here we use cursor to fetch the stored procedure one by one from SQL Server and remove from the list.

Query:

DECLARE @spName varchar(1000)

DECLARE spcurs cursOR

FOR SELECT [name] FROM sys.objects WHERE TYPE = 'p' -- p defines the stored procedure

OPEN spcurs

FETCH NEXT FROM spcurs INTO @spName

while @@fetch_status = 0

BEGIN

    exec('DROP PROCEDURE ' + @spName)

    print 'Deleted procedure -> '+ @spName

    FETCH NEXT  FROM spcurs INTO @spName

END

CLOSE spcurs

DEALLOCATE spcurs