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

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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 - Sql

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 - Sql

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

You can delete Tables or Views in the same manner.

Solution 4 - Sql

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 - Sql

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);

Solution 6 - Sql

To get drop statements for all stored procedures in a database SELECT 'DROP PROCEDURE' + ' '

F.NAME + ';' FROM SYS.objects AS F where type='P'

Solution 7 - Sql

DECLARE @sql VARCHAR(MAX)
SET @sql=''
SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects
WHERE type = 'p' AND  is_ms_shipped = 0
exec(@sql);

Solution 8 - Sql

Try this, it work for me

DECLARE @spname sysname;
DECLARE SPCursor CURSOR FOR
SELECT SCHEMA_NAME(schema_id) + '.' + name
FROM sys.objects
WHERE type = 'P';
OPEN SPCursor;
FETCH NEXT FROM SPCursor INTO @spname;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP PROCEDURE ' + @spname);
FETCH NEXT FROM SPCursor INTO @spname;
END
CLOSE SPCursor;
DEALLOCATE SPCursor;

Solution 9 - Sql

DECLARE @DeleteProcCommand NVARCHAR(500)

DECLARE Syntax_Cursor CURSOR
FOR
SELECT 'DROP PROCEDURE ' + p.NAME
FROM sys.procedures p

OPEN Syntax_Cursor

FETCH NEXT FROM Syntax_Cursor

INTO @DeleteProcCommand

WHILE (@@FETCH_STATUS = 0)
BEGIN

EXEC (@DeleteProcCommand)

FETCH NEXT FROM Syntax_Cursor
INTO @DeleteProcCommand

END

CLOSE Syntax_Cursor

DEALLOCATE Syntax_Cursor

Solution 10 - Sql

Try this:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DROP PROCEDURE dbo.'
  + QUOTENAME(name) + ';
' FROM sys.procedures
WHERE name LIKE N'spname%'
AND SCHEMA_NAME(schema_id) = N'dbo';

EXEC sp_executesql @sql;

Solution 11 - Sql

ANSI compliant, without cursor

DECLARE @SQL national character varying(MAX) 
SET @SQL= ''

SELECT @SQL= @SQL+ N'DROP PROCEDURE "' + REPLACE(SPECIFIC_SCHEMA, N'"', N'""') + N'"."' + REPLACE(SPECIFIC_NAME, N'"', N'""') + N'"; '
FROM INFORMATION_SCHEMA.ROUTINES 

WHERE (1=1) 
AND ROUTINE_TYPE = 'PROCEDURE' 
AND ROUTINE_NAME NOT IN 
(
     'dt_adduserobject'
    ,'dt_droppropertiesbyid'
    ,'dt_dropuserobjectbyid'
    ,'dt_generateansiname'
    ,'dt_getobjwithprop'
    ,'dt_getobjwithprop_u'
    ,'dt_getpropertiesbyid'
    ,'dt_getpropertiesbyid_u'
    ,'dt_setpropertybyid'
    ,'dt_setpropertybyid_u'
    ,'dt_verstamp006'
    ,'dt_verstamp007'

    ,'sp_helpdiagrams'
    ,'sp_creatediagram'
    ,'sp_alterdiagram'
    ,'sp_renamediagram'
    ,'sp_dropdiagram'

    ,'sp_helpdiagramdefinition'
    ,'fn_diagramobjects'
    ,'sp_upgraddiagrams'
) 


ORDER BY SPECIFIC_NAME 


-- PRINT @SQL
EXEC(@SQL)

Without cursor, non-ansi compliant:

DECLARE @sql NVARCHAR(MAX) = N''
, @lineFeed NVARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + N'DROP PROCEDURE ' + QUOTENAME(SPECIFIC_SCHEMA) + N'.' + QUOTENAME(SPECIFIC_NAME) + N';' + @lineFeed
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE' 
-- AND SPECIFIC_NAME LIKE 'sp[_]RPT[_]%'
  

AND ROUTINE_NAME NOT IN 
( 
    SELECT name FROM sys.procedures WHERE is_ms_shipped <> 0 
) 


ORDER BY SPECIFIC_NAME 


-- PRINT @sql 
EXECUTE(@sql)

Solution 12 - Sql

By mixing the cursor and system procedure, we would have a optimized solution, as follow:

DECLARE DelAllProcedures CURSOR
FOR
    SELECT name AS procedure_name 
	FROM sys.procedures;
OPEN DelAllProcedures
DECLARE @ProcName VARCHAR(100)
FETCH NEXT 
FROM DelAllProcedures
INTO @ProcName
WHILE @@FETCH_STATUS!=-1
BEGIN 
    DECLARE @command VARCHAR(100)
	SET @command=''
    SET @command=@command+'DROP PROCEDURE '+@ProcName
	--DROP PROCEDURE  @ProcName
    EXECUTE (@command)
	FETCH NEXT 
    FROM DelAllProcedures
	INTO @ProcName
END
CLOSE DelAllProcedures
DEALLOCATE DelAllProcedures

Solution 13 - Sql