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