未加星标

Drop SQL Server Views with N Levels of Dependencies

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
Problem

I have SQL Server 2014 instance with one database with more than 300 schema binding views. Many of the SQL Server views have interdependencies and some have N level of dependencies. I want to drop all the views in my database related to our products. Unfortunately, I get the following error: "Cannot drop view because it is being reference by view...". I am having issues determining the correct drop view sequence in this scenario. How can I drop these SQL Server views?

Solution

Check out the script below for dropping all views simultaneously even if N level dependencies exist. Run the below script in SQL Server Management Studio in a Development or Test environment prior to production. Please review the inline comments for each section of code for a better understanding of each code block.

Drop All SQL Server Views Script SET NOCOUNT ON
/*Declare local variable*/
DECLARE @rowCount INT,
@viewList NVARCHAR(MAX),
@minid INT,
@maxid INT,
@viewName NVARCHAR(MAX)
/*Declare local temp table*/
CREATE TABLE #allviews
(
childview NVARCHAR(2000) COLLATE database_default,
parentview NVARCHAR(2000) COLLATE database_default
)
CREATE TABLE #childParentAllviews
(
ID INT PRIMARY KEY IDENTITY(1,1),
childview NVARCHAR(2000) COLLATE database_default,
parentview NVARCHAR(2000) COLLATE database_default
)
CREATE TABLE #firstlevelrecursionview
(
ID INT PRIMARY KEY IDENTITY(1,1),
vpath NVARCHAR(MAX),
childview NVARCHAR(2000) COLLATE database_default,
parentview NVARCHAR(2000) COLLATE database_default
)
CREATE TABLE #hierarchywiseorder
(
ID INT PRIMARY KEY IDENTITY(1,1),
ViewName NVARCHAR(MAX) COLLATE database_default
)
CREATE TABLE #finalhierarchywiseorder
(
ID INT PRIMARY KEY IDENTITY(1,1),
viewname NVARCHAR(MAX) COLLATE database_default
)
/*Insert all schemabinding views with reference view in #allviews table*/
INSERT INTO #allviews
(
childview,
parentview
)
SELECT CAST(sschema.name + N'.' + sview.name AS NVARCHAR(2000)),
CAST(rschema.name + N'.' + rview.name AS NVARCHAR(2000))
FROM sys.views AS sview
INNER JOIN sys.schemas AS sschema ON sview.[schema_id] = sschema.[schema_id]
CROSS APPLY sys.dm_sql_referencing_entities (sschema.name + N'.' + sview.name, N'OBJECT') AS refentities
INNER JOIN sys.views AS rview ON refentities.referencing_id = rview.[object_id]
INNER JOIN sys.schemas AS rschema ON rview.[schema_id] = rschema.[schema_id]
WHERE OBJECTPROPERTY(OBJECT_ID(CAST(sschema.name + N'.' + sview.name AS NVARCHAR(2000))), 'IsSchemaBound') = 1
/*Using cte create vpath for purpose of view order sequence and with using this path we can easily collect all child with parent view data */
;WITH Parentview
AS
(
SELECT cast(childview AS NVARCHAR(2000)) as vpath,
childview,
parentview
FROM #allviews
UNION ALL
SELECT cast(parentview.vpath + '/' + cast(allview.childview AS NVARCHAR(2000)) AS NVARCHAR(2000)) vpath,
allview.childview,
allview.parentview
FROM #allviews allview
INNER JOIN Parentview parentview on parentview.parentview = allview.childview
)
INSERT INTO #firstlevelrecursionview
(
vpath,
childview,
parentview
)
SELECT vpath,
childview,
parentview
FROM Parentview
OPTION (MAXRECURSION 0)
/*First of all we inserted parent views in table #childParentAllviews*/
INSERT INTO #childParentAllviews
(
childview,
parentview
)
SELECT DISTINCT childview,
parentview
FROM #firstlevelrecursionview
WHERE vPath NOT LIKE '%/%'
AND childview <> parentview
/*Finding all parent,child sequence of views for dropping purpose for Example if views relation like A->B->C so script collect views like
C->B,C->A,B->A and insert in table childParentAllviews*/
IF(EXISTS(SELECT 1
FROM #firstlevelrecursionview
WHERE vPath LIKE '%/%'
)
)
BEGIN
INSERT INTO #childParentAllviews
(
childview,
parentview
)
SELECT t1.Childview,
t1.parentview
FROM
(
SELECT DISTINCT tra.a.value('.', 'NVARCHAR(200)') AS Childview,
childview parentview
FROM
(
SELECT CAST('<M>' + REPLACE(vPath, '/', '</M><M>') + '</M>' AS XML) AS list,
childview
FROM #firstlevelrecursionview
WHERE vPath LIKE '%/%'
)a
CROSS APPLY list.nodes('/M') AS tra ( A )
)T1
WHERE t1.Childview <> t1.parentview
END
/*Till we collect only views with all parent view now arrange all views with hiererchy order wise and insert it into table #hierarchywiseorder*/
PARENTRECORDS:INSERT INTO #hierarchywiseorder
(
viewName
)
SELECT DISTINCT childparentview.parentview
FROM #childParentAllviews childparentview
LEFT JOIN #childParentAllviews childparentview2 ON childparentview.parentview = childparentview2.childview
AND childparentview2.parentview IS NOT NULL
WHERE childparentview2.id IS NULL
AND ISNULL(childparentview.parentview,'') <> ''
AND NOT EXISTS(
SELECT 1
FROM #hierarchywiseorder
WHERE viewname = childparentview.parentview
)
SET @rowCount = @@ROWCOUNT
UPDATE childparentview
SET parentview = NULL
FROM #childParentAllviews childparentview
LEFT JOIN #childParentAllviews childparentview2 on childparentview.parentview = childparentview2.childview
AND childparentview2.parentview IS NOT NULL
WHERE childparentview2.id IS NULL
AND ISNULL(childparentview.parentview,'') <> ''
IF @rowCount > 0
BEGIN
GOTO PARENTRECORDS
END
INSERT INTO #hierarchywiseorder
(
viewName
)
SELECT DISTINCT childparentview.childview
FROM #childParentAllviews childparentview
WHERE NOT EXISTS(
SELECT 1
FROM #hierarchywiseorder
WHERE viewname = childparentview.childview
)
/*Now finally first we insert non relational views in table #finalhierarchywiseorder */
INSERT INTO #finalhierarchywiseorder
(
viewname
)
SELECT DISTINCT CAST(sschema.name + N'.' + sview.name AS NVARCHAR(2000))
FROM sys.views AS sview
INNER JOIN sys.schemas AS sschema ON sview.[schema_id] = sschema.[schema_id]
LEFT JOIN #hierarchywiseorder horder ON horder.ViewName = CAST(sschema.name + N'.' + sview.name AS NVARCHAR(2000))
WHERE horder.id is null
/*All dependent views with order add in table #finalhierarchywiseorder*/
INSERT INTO #finalhierarchywiseorder
SELECT ViewName
FROM #hierarchywiseorder
ORDER BY id ASC
/*Now all views collect with order and Print it one by one with using loop*/
SELECT @minid = MIN(id),
@maxid = MAX(id)
FROM #finalhierarchywiseorder
WHILE @minid <= @maxid
BEGIN
SELECT @viewName = viewname
FROM #finalhierarchywiseorder
WHERE id = @minid
IF( EXISTS
(
SELECT 1
FROM sys.views AS sview
INNER JOIN sys.schemas AS sschema ON sview.[schema_id] = sschema.[schema_id]
WHERE CAST(sschema.name + N'.' + sview.name AS NVARCHAR(2000)) = @viewName
)
)
BEGIN
PRINT('DROP VIEW '+ @viewName +'')
END
SET @minid = @minid + 1
END
DROP TABLE #allviews
DROP TABLE #childParentAllviews
DROP TABLE #firstlevelrecursionview
DROP TABLE #hierarchywiseorder
DROP TABLE #finalhierarchywiseorder
SET NOCOUNT OFF Next Steps Please test in this code in a Development or Test environment before Production. Check out all tips related toSQL Server Views.

Last Update: 1/2/2017


Drop SQL Server Views with N Levels of Dependencies
Drop SQL Server Views with N Levels of Dependencies
About the author
Drop SQL Server Views with N Levels of Dependencies
Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA. View all my tips

Related Resources

More Database Developer Tips...

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerTIXMLRYRIMSNUCUNTUOPT
分页:12
转载请注明
本文标题:Drop SQL Server Views with N Levels of Dependencies
本站链接:http://www.codesec.net/view/520045.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(94)