Delete all Stored Procedures at once
Solution 1
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.procedures
WHERE name LIKE N'sp[_]%'
AND SCHEMA_NAME(schema_id) = N'dbo';
EXEC sp_executesql @sql;
Solution 2
-- 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
Mahdi Tahsildari
Love programming, it's fun. MCP since 2011. C#, ASP.net MVC Core, SQL; NodeJs, Angular, HTML, CSS, Javascript. Currently focused on ASP.Net Core and Unit Testing.
Updated on July 12, 2022Comments
-
Mahdi Tahsildari almost 2 years
I have 200
Stored Procedures
in mySql server 2008 R2
database that are automatically generated by an application. Now I want to delete them all and regenerate them because the tables have changed a lot.This question is very similar to my case but in my case all the SP's start with sp_ and I thinks it's dangerous to use the same code since system SP's also start with sp_ and I may kill them all.
Should I trust the solution in the link above? If not is there any safer solution?
-
JonBrave over 7 yearsBeing able to use a single
SELECT
plusEXEC
instead of having to declareCURSOR
and iterate through is really neat. I did not know that T-SQL allowed you to build up a string this way (SELECT @sql +=
), very clever. -
Kenneth Lauritsen over 6 yearsWas looking for a solution like this. I do have a few tweaks to the query. DECLARE \@sql NVARCHAR(MAX) = N''; SELECT \@sql += N'DROP PROCEDURE [' + SCHEMA_NAME(p.[schema_id]) + '].' + QUOTENAME(p.[name]) + ';' FROM sys.procedures p WHERE p.is_ms_shipped = 0 --AND SCHEMA_NAME(p.[schema_id]) IN (N'dbo') EXEC sp_executesql \@sql; The query is no longer hardcoded to the dbo schema. Also i added a filter to avoid deleting system stored procedures. There is also a criteria to limit by schema(s) though i didn't need it in my case.