Today I wrote a procedure to drop all views from a schema (and the schema itself) in SQL Server 2005. I use views to expose objects to SSAS DSV and it's useful having a cleanup code. Here's the script: DropSchema drop all the views and the schema itself, DropSchemaViews is called by DropSchema and drop all the views within the specified schema. No error checking code, use it at your own risk!

IF EXISTS(SELECT * FROM sys.procedures WHERE NAME = 'DropSchema' AND SCHEMA_ID = 1) DROP PROCEDURE dbo.DropSchema
GO
IF EXISTS(SELECT * FROM sys.procedures WHERE NAME = 'DropSchemaViews' AND SCHEMA_ID = 1) DROP PROCEDURE dbo.DropSchemaViews
GO

CREATE PROCEDURE dbo.DropSchemaViews( @schema NVARCHAR(128) )
AS BEGIN

    DECLARE @view NVARCHAR(256)
    DECLARE @cmd NVARCHAR(500) 

    DECLARE v CURSOR FOR
        SELECT name 
        FROM sys.VIEWS
        WHERE SCHEMA_ID = 
                  (SELECT schema_id 
                   FROM sys.schemas 
                   WHERE NAME = @schema)
    OPEN v
    FETCH NEXT FROM v INTO @view

    WHILE @@fetch_status = 0 BEGIN
        SET @cmd = N'DROP VIEW [' + @schema + N'].[' + @view + N']'
        EXEC ( @cmd )
        FETCH NEXT FROM v into @view
    END
    CLOSE v
    DEALLOCATE v

END
GO

CREATE PROCEDURE dbo.DropSchema( @schema NVARCHAR(128) )
AS BEGIN

    DECLARE @view NVARCHAR(256)
    DECLARE @cmd NVARCHAR(500) 

    EXEC DropSchemaViews @schema 

    IF EXISTS(SELECT schema_id 
              FROM sys.schemas 
              WHERE NAME = @schema)
    BEGIN
        SET @cmd = N'DROP SCHEMA [' + @schema + N']'
        EXEC ( @cmd )
    END
    
END
GO