posted on Wednesday, May 30, 2007 4:33 PM
by
marcorusso
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!
1: IF EXISTS(SELECT * FROM sys.procedures WHERE NAME = 'DropSchema' AND SCHEMA_ID = 1) DROP PROCEDURE dbo.DropSchema
2: GO
3: IF EXISTS(SELECT * FROM sys.procedures WHERE NAME = 'DropSchemaViews' AND SCHEMA_ID = 1) DROP PROCEDURE dbo.DropSchemaViews
4: GO
5: CREATE PROCEDURE dbo.DropSchemaViews( @schema NVARCHAR(128) )
6: AS BEGIN
7: DECLARE @view NVARCHAR(256)
8: DECLARE @cmd NVARCHAR(500)
9: DECLARE v CURSOR FOR
10: SELECT name
11: FROM sys.VIEWS
12: WHERE SCHEMA_ID =
13: (SELECT schema_id
14: FROM sys.schemas
15: WHERE NAME = @schema)
16: OPEN v
17: FETCH NEXT FROM v INTO @view
18: WHILE @@fetch_status = 0 BEGIN
19: SET @cmd = N'DROP VIEW [' + @schema + N'].[' + @view + N']'
20: EXEC ( @cmd )
21: FETCH NEXT FROM v into @view
22: END
23: CLOSE v
24: DEALLOCATE v
25: END
26: GO
27: CREATE PROCEDURE dbo.DropSchema( @schema NVARCHAR(128) )
28: AS BEGIN
29: DECLARE @view NVARCHAR(256)
30: DECLARE @cmd NVARCHAR(500)
31: EXEC DropSchemaViews @schema
32: IF EXISTS(SELECT schema_id
33: FROM sys.schemas
34: WHERE NAME = @schema)
35: BEGIN
36: SET @cmd = N'DROP SCHEMA [' + @schema + N']'
37: EXEC ( @cmd )
38: END
39:
40: END
41: GO
Cross-posted from SQLBlog! -
http://www.sqlblog.com