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
VN:F [1.9.22_1171]
Rating: 7.1/10 (23 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 3 votes)
How to Drop All Stored Procedures in Your Database, 7.1 out of 10 based on 23 ratings
Be Sociable, Share!