Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Drop SQL Server Views with N Levels of Dependencies


By:   |   Read Comments (7)   |   Related Tips: More > Views

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 to SQL Server Views.


Last Update:






About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, May 03, 2017 - 12:33:25 AM - K.B Patel Back To Top

Sir This tips is very useful for me. We faced many issue related it  in our product and we solved out issue using it. This tips is working good when schemabinding views are isolated means till not used in schembinding function.but if both are dependent then its not work please suggest.

 

 


Thursday, January 26, 2017 - 11:21:03 PM - Patel Bhavesh Back To Top

Thanks @jnm2 for suggestion approached but Some times multiple CTE  recursion is being made performance blocker.

Here I applied your approached script in my DB and not got result after ran 2 hour waiting.


Wednesday, January 04, 2017 - 8:31:43 AM - Ankit Patel Back To Top

 

Good Job..

 its very helpful..

 


Tuesday, January 03, 2017 - 8:55:49 AM - Payal Mulani Back To Top

 Great .It's very helpful. Thanks.

 


Tuesday, January 03, 2017 - 12:53:35 AM - Jignesh Raiyani Back To Top

 

Great..!! its very helpful..


Monday, January 02, 2017 - 7:09:59 PM - jnm2 Back To Top

I recently did something similar, but without using temporary tables. It's a single query which uses a recursive self-join and counts how many times recursion happens, then sorts by recursion depth and then by original order for a stable sort.

https://gist.github.com/jnm2/faadcdd7ffe92e38b9cea0d92d5e1e99


Monday, January 02, 2017 - 9:31:03 AM - Chandresh Patel Back To Top

 

Good Job... really very usefull while you want to drop object and it is SCHEMABINDING...!

 

 


Learn more about SQL Server tools