[SOLVED] Delete all Stored Procedures at once

Delete all Stored Procedures at once

I have 200 Stored Procedures in my Sql 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?

Solution

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:

In modern versions:

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CONVERT(nvarchar(max), 
   CONCAT(N'DROP PROCEDURE ', QUOTENAME(s.name)
  + N'.' + QUOTENAME(p.name) + ';')), char(13)+char(10))
 FROM sys.procedures AS p
 INNER JOIN sys.schemas AS s
    ON p.[schema_id] = s.[schema_id]
 WHERE p.name LIKE N'sp[_]%';

EXEC sys.sp_executesql @sql;

In ancient versions, you can use the += trick, but note that it has some potential downsides, such as unexpected truncation, .

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'DROP PROCEDURE ' 
  + QUOTENAME(s.name)
  + N'.' + QUOTENAME(p.name) + ';
' FROM sys.procedures AS p
 INNER JOIN sys.schemas AS s
    ON p.[schema_id] = s.[schema_id]
 WHERE p.name LIKE N'sp[_]%';

EXEC sys.sp_executesql @sql;

To see the problems (and other ways to do it):