Drop views from a schema

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

Originally appeared on: http://sqlblog.com/blogs/marco_russo/archive/2007/05/30/drop-views-from-a-schema.aspx