Drop SQL Server Views with N Levels of Dependencies
By: Bhavesh Patel | Comments (7) | Related: 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.