Delete all Stored Procedures at once

If this is a one- time task, just open Object Explorer, expand your database > programmability and highlight the Stored Procedures node. Then turn on Object Explorer Details (F7 I think). On the right you should see your list, and here you can multi-select - so you can sort by name, choose all procedures that start with sp_, and delete them all with one keystroke.

If you are doing this repeatedly, then (assuming your procedures are all in the dbo schema):

DECLARE @sql NVARCHAR(MAX) = N'';SELECT @sql += N'DROP PROCEDURE dbo.'  + QUOTENAME(name) + ';' FROM sys.proceduresWHERE name LIKE N'sp[_]%'AND SCHEMA_NAME(schema_id) = N'dbo';EXEC sp_executesql @sql;

-- drop all user defined stored procedures    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      Exec('drop procedure ' + @procName)      Fetch Next From cur Into @procName     End    Close cur     Deallocate cur 

I found this How to Drop All Stored Procedures in Your Database then I tested and the StoredProcedure is created at outside of Stored Procedures folder.

CREATE PROC UserStoredProcedure_Sample1 AS       SELECT 'SQL Server rocks'  GOCREATE PROC UserStoredProcedure_Sample2 AS     SELECT 'SQL Server rocks' GO SET NOCOUNT ON  -- to do this we have to use EXEC instead of sp_executesql -- sp_executesql does not accept a DROP command in the SQL String DECLARE @UserStoredProcedure    VARCHAR(100) DECLARE @Command                    VARCHAR(100) DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR SELECT     SPECIFIC_NAME FROM    INFORMATION_SCHEMA.ROUTINES  OPEN UserStoredProcedureCursorFETCH NEXT FROM UserStoredProcedureCursor INTO @UserStoredProcedure  WHILE (@@FETCH_STATUS = 0) BEGIN        SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure         -- display; visual check         SELECT @Command        -- when you are ready to execute, uncomment below        EXEC (@Command)        FETCH NEXT FROM UserStoredProcedureCursor         INTO @UserStoredProcedure  END CLOSE UserStoredProcedureCursor DEALLOCATE UserStoredProcedureCursor   SET NOCOUNT OFF