How to drop all the user stored procedures from a database

A very informative script is here. But since the original posted script has very annoying line numbers, I would rather re-post the same code here again. And now you can copy and paste it to SSMS very easily. Also something worth mentioning is that the INFORMATION_SCHEMA.ROUTINES view actually contains user defined functions' info, so the SQL command "Drop procedure" will fail if you have any function defined in your target database. Therefore, I changed the following script to use sys.sysobjects view to retrieve the full list of names of user stored procedures.


-- this sets up the test database

-- Drop the database if it already existt

USE master
GO

IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'testdb'
)

DROP DATABASE testdb
GO

CREATE DATABASE testdb
GO

USE testdb
GO

CREATE PROC UserStoredProcedure_Sample1
AS
SELECT 'SQL Server rocks'
GO

CREATE PROC UserStoredProcedure_Sample2
AS
SELECT 'SQL Server rocks'
GO

SET NOCOUNT ON
-- to do this we have to use EXEC instead of sp_executesql

-- sp_executesql does not accept a DROP command in the SQL String

DECLARE @UserStoredProcedure VARCHAR(100)
DECLARE @Command VARCHAR(100)


DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR
select name from sys.sysobjects where xtype = 'P'

/*
SELECT
SPECIFIC_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
*/


OPEN UserStoredProcedureCursor


FETCH NEXT FROM UserStoredProcedureCursor
INTO @UserStoredProcedure
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure
-- display; visual check

SELECT @Command
-- when you are ready to execute, uncomment below

EXEC (@Command)

FETCH NEXT FROM UserStoredProcedureCursor

INTO @UserStoredProcedure

END

CLOSE UserStoredProcedureCursor

DEALLOCATE UserStoredProcedureCursor

SET NOCOUNT OFF