THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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

 

Published Wednesday, May 30, 2007 4:33 PM by Marco Russo (SQLBI)
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement