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