How to drop all stored procedures at once in SQL Server database?

Currently we use separate a drop statements for each stored procedure in the script file:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]')   
  AND type in (N'P', N'PC'))  
DROP PROCEDURE [dbo].[MySP] 

Is there a way to drop them all at once, or maybe in a loop?

Solution 1:

I would prefer to do it this way:

• first generate the list of stored procedures to drop by inspecting the system catalog view:

  SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];'
  FROM sys.procedures p 
  

  This generates a list of DROP PROCEDURE statements in your SSMS output window.

• copy that list into a new query window, and possibly adapt it / change it and then execute it

No messy and slow cursors, gives you the ability to check and double-check your list of procedure to be dropped before you actually drop it

Solution 2:

Something like (Found at Delete All Procedures from a database using a Stored procedure in SQL Server).

Just so by the way, this seems like a VERY dangerous thing to do, just a thought...

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

Solution 3:

1. In the Object Explorer pane, select the Stored Procedures folder.
2. Press F7 (or from the main menu, choose View > Object Explorer Details).
3. Select all procedures except the System Table.
4. Press Delete button and select OK.

You can delete Tables or Views in the same manner.

Solution 4:

create below stored procedure in your db(from which db u want to delete sp's)

then right click on that procedure - click on Execute Stored Procedure..

then click ok.

create Procedure [dbo].[DeleteAllProcedures]
As 
declare @schemaName varchar(500)    
declare @procName varchar(500)
declare cur cursor
for select s.Name, p.Name from sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
ORDER BY s.Name, p.Name
open cur

fetch next from cur into @schemaName,@procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @schemaName + '.' + @procName)
fetch next from cur into @schemaName,@procName
end
close cur
deallocate cur

Solution 5:

I think this is the simplest way:

DECLARE @sql VARCHAR(MAX)='';

SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects 
WHERE type = 'p' AND  is_ms_shipped = 0

exec(@sql);