Dropping all stored procedures, functions, views and also system-generated constraints

The script below will drop all stored procedures, functions and views in the database. Very useful when doing upgrades. It will also drop all system-generated default constraints. Just make sure that from now on, in your DB upgrade script, all the constraints have predefined names.

Works in SQL Server 2008

DECLARE @schemaName varchar(100), @objName varchar(500), @objType varchar(2), @parentObjName varchar(500)
DECLARE cur CURSOR
FOR SELECT sa.name AS SchemaName, s.[name] AS ObjName, s.[type] AS ObjType, s2.[name] AS ParentObjName
    FROM sys.objects s
    INNER JOIN sys.schemas sa ON s.schema_id = sa.schema_id
    LEFT OUTER JOIN sys.objects s2 ON s2.object_id = s.parent_object_id   
    WHERE s.[name] NOT LIKE '%aspnet%' AND s.[type] in (N'P', N'PC', N'V', N'FN', N'TF') OR (s.[name] like '%[_][_]%' AND s.[name] NOT LIKE '%aspnet%')

--ignoring constraints on ASP.Net tables as they are not touched by the upgrade
OPEN cur

FETCH NEXT FROM cur INTO @schemaName, @objName, @objType, @parentObjName
WHILE @@fetch_status = 0
BEGIN
    PRINT 'Dropping ['+@objName+'] ...'
    IF @objType = N'V'
    BEGIN
        EXEC (N'DROP VIEW ['+@schemaName+'].['+@objName+']')
    END
    IF @objType IN (N'P', N'PC')
    BEGIN
        EXEC (N'DROP PROCEDURE ['+@schemaName+'].['+@objName+']')
    END
    IF @objType IN (N'FN', N'TF')
    BEGIN
        EXEC (N'DROP FUNCTION ['+@schemaName+'].['+@objName+']')
    END   
    IF @objType IN (N'D') AND @parentObjName IS NOT NULL
    BEGIN
        PRINT 'Will drop the '+ @objName +' constraint in ' + @parentObjName
        EXEC (N'ALTER TABLE ['+@schemaName+'].['+@parentObjName+'] DROP CONSTRAINT ['+@objName+']');
    END       
    FETCH NEXT FROM cur INTO @schemaName, @objName, @objType, @parentObjName
END
CLOSE cur
DEALLOCATE cur
GO