How to Drop All Stored Procedures in Your Database

One of my dev friends asked me how he can drop all the stored procedures in his test database.

One way to do it is by using a cursor to get the names of the stored procedures in a system table, INFORMATION_SCHEMA table or DMV, and then executing a dynamic query that drops the sproc.

   1: -- this sets up the test database
   2: -- Drop the database if it already exists
   3: USE master
   4: GO
   5:
   6: IF  EXISTS (
   7:     SELECT name
   8:     FROM sys.databases
   9:     WHERE name = N'testdb'
  10: )
  11: DROP DATABASE testdb
  12: GO
  13:
  14: CREATE DATABASE testdb
  15: GO
  16: USE testdb
  17: GO
  18:
  19: CREATE PROC UserStoredProcedure_Sample1
  20: AS
  21:     SELECT 'SQL Server rocks'
  22: GO
  23:
  24: CREATE PROC UserStoredProcedure_Sample2
  25: AS
  26:     SELECT 'SQL Server rocks'
  27: GO
  28:
  29:
  30:
  31:
  32: SET NOCOUNT ON
  33:
  34: -- to do this we have to use EXEC instead of sp_executesql
  35: -- sp_executesql does not accept a DROP command in the SQL String
  36: DECLARE @UserStoredProcedure    VARCHAR(100)
  37: DECLARE @Command                    VARCHAR(100)
  38:
  39: DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR
  40: SELECT
  41:     SPECIFIC_NAME
  42: FROM
  43:     INFORMATION_SCHEMA.ROUTINES
  44:
  45: OPEN UserStoredProcedureCursor
  46:
  47: FETCH NEXT FROM UserStoredProcedureCursor
  48: INTO @UserStoredProcedure
  49: WHILE (@@FETCH_STATUS = 0) BEGIN
  50:        SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure
  51:
  52:          -- display; visual check
  53:          SELECT @Command
  54:
  55:        -- when you are ready to execute, uncomment below
  56:        EXEC (@Command)
  57:
  58:        FETCH NEXT FROM UserStoredProcedureCursor
  59:        INTO @UserStoredProcedure
  60: END
  61:
  62:
  63: CLOSE UserStoredProcedureCursor
  64: DEALLOCATE UserStoredProcedureCursor
  65:
  66: SET NOCOUNT OFF
Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s