Drop SQL Server Views with N Levels of Dependencies

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