Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 892
Mahdi Tahsildari Avatar asked Jan 20 '13 12:01

Mahdi Tahsildari


People also ask

How do I clear an entire SQL database?

Using SQL Server Management StudioIn Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand Databases, right-click the database to delete, and then click Delete. Confirm the correct database is selected, and then click OK.


2 Answers

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;
like image 96
Aaron Bertrand Avatar answered Oct 31 '22 05:10

Aaron Bertrand


-- 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 
like image 23
Bhavsar Jay Avatar answered Oct 31 '22 07:10

Bhavsar Jay