USE YourDatabase
GO

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM SYSOBJECTS 
WHERE 	(	
		(type = 'P' AND (name LIKE 'usp[_]%' OR name like 'isp[_]%'))
		OR
		(type = 'FN' AND name LIKE 'udf[_]%')
		OR
		(type = 'TF' AND name LIKE 'udf[_]%')
		OR
		(type = 'U')
		OR
		(type = 'V' AND name LIKE 'v[_]%')
		OR
		(type = 'TR')
	)
	AND 
		uid = 1
	AND
		status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @objType IN ('TF', 'FN')
		EXEC ('DROP FUNCTION dbo.' + @objName)

	IF @objType = 'P'
		EXEC ('DROP PROC dbo.' + @objName)

	IF @objType = 'V'
		EXEC ('DROP VIEW dbo.' + @objName)

	IF @objType = 'TR'
		EXEC ('DROP TRIGGER dbo.' + @objName)

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO

SELECT 'DROP PROC ' + name + char(10) + char(13) + 'GO'
from sysobjects where type = 'p'
order by name