Dropping Multiple Stored Procedures

Occasionally when you're working with a database, e.g. your local dev environment, you need to do a little bit of cleanup. Let's say you run a stored procedure creation script from with SQL Server Management Studio and forget to specify the target database. So instead of having a bunch of procedures in the intended database you now have them in another database or, perhaps worse, in your master database.

SSMS doesn't allow you to select multiple procs to run commands on at once, so you have to select them one-by-one and issue the drop. I don't know about you, but there's no way I want to do that for ten, let alone fifty or more stored procedures. A better way to approach this is to instead write a batch processing script that loops over a list of stored procedure names and executes the DROP command on each of them.

The trick here is to get a list of user-defined stored procedures that fall under the desired catalog (database) and loop over that list with a cursor. The reason for this is that unlike a WHERE clause you can't pass a list to the DROP PROCEDURE command. To accomplish a batch DROP, you have to store the resulting value from the current row retrieved from the cursor and execute the command on each value inside a loop. The complete code is shown below.

* Please be advised this code is provided as is with no warranties of any kind. Don't run this on a production environment if you're not absolutely certain as to how it will affect your database system.

--
-- DROP USER-DEFINED STORED PROCEDURES
--

USE MASTER; -- Change to desired database
GO
SET NOCOUNT ON;

PRINT '---- STARTING ----';

DECLARE @UserStoredProcedureName    VARCHAR(200),
        @Command                    VARCHAR(200);

PRINT '---- Creating Cursor... ----';

DECLARE SPCursor CURSOR SCROLL STATIC READ_ONLY FOR
SELECT SPECIFIC_NAME 
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE SPECIFIC_CATALOG = 'master' -- Change to desired database
   AND SPECIFIC_NAME IN 
   -- This inner select is likely redundant, but I'm not clear on all the 
   -- differences between sys.objects and INFORMATION_SCHEMA
 (
    SELECT name FROM sys.objects
     WHERE type='P'
       AND is_ms_shipped = 0 
       -- A value of 0 represents user-defined procs
       -- Please note, some diagram utility stored procs don't follow this rule
 );

PRINT '---- ...Finished Creating Cursor ----'

PRINT '---- Opening Cursor... ----';

OPEN SPCursor;

PRINT '---- ...Finished Opening Cursor ----';

PRINT '---- Retrieving First Rec... ----';

FETCH NEXT FROM SPCursor
INTO @UserStoredProcedureName;

PRINT '---- ...Finished Retrieving First Rec ----';

PRINT '---- Starting Command Loop... ----';

WHILE(@@FETCH_STATUS = 0)
BEGIN

    -- Include a schema name as a prefix, if necessary
    SET @Command = 'DROP PROCEDURE dbo.' + @UserStoredProcedureName;
    
    PRINT '----     executing:: ' + @Command;
    
    --SELECT @Command
    
    --***Only uncomment the line below if you're sure the correct procs will be affected***
    --EXEC(@Command)
    
    FETCH NEXT FROM SPCursor
    INTO @UserStoredProcedureName;
    
END;

PRINT '---- ...Finished Command Loop ----';

PRINT '---- Closing Cursor... ----';

CLOSE SPCursor;

PRINT '---- ...Done Closing Cursor ----';

PRINT '---- Deallocating Cursor... ----';

DEALLOCATE SPCursor;

PRINT '---- ...Done Closing Cursor ----';

PRINT '---- FINISHED ----';

SET NOCOUNT OFF;