Delete All Store Procedures in SQL SERVER

Share |

While working on SQL Server 2008, there was a situation when I had to delete one Store Procedure at a time :(.
After doing some research to delete all store procedures with some query, I succeeded to save 2-3 hours or labour work :P

To delete all store procedures in single we can use following code in SQL Query.

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'p'
      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @procedureName)
            FETCH NEXT FROM cur INTO @procedureName
      END
      CLOSE cur
      DEALLOCATE cur

This will surely save time, required to Delete All Store Procedures in SQL SERVER Database