From the Vault: Detangling Nested Views

I’ll be in France and largely AFK for a few weeks this summer, so I’m posting some golden oldies. You can find the original article and comments here

I hate nested views. I hate them, I hate them, I hate them. This is the Nth shop I have been in that makes use of one view that pulls FROM and JOINs other views, which in turn pull from other views, and it’s foot-stompingly frustrating to work with.

I’ve talked before about how inappropriate views hurt performance, and I will talk in the future specifically about how nested views hurt performance, but today I just want to give you the start of a solution.  In time, I hope to develop this code into something beautiful, into a tool that will rewrite nested views into their simplest components, whilst simultaneously washing your dog and paying your overdue utility bills. But for now, this is what we have.

Note: This script is workable, but not complete. It doesn’t currently take different schemas into account, and there’s a lot more functionality I want to add to it.  Consider this a draft, or an early beta, and use it as intended.

A nested view is like a tree: the top node (the parent view) references one or more child nodes (child tables and views), which in turn may reference another level of child nodes, and so on.  Here we have a script that takes a given view and works through each level of nodes, to ferret out all the referenced objects:

----------------------------------------------------------------------------
-- Create temp table, variables
----------------------------------------------------------------------------
-- Create a temp table to hold the view/table hierarchy
CREATE TABLE #viewHierarchy
( id INT IDENTITY(1,1)
, parent_view_id INT
, referenced_schema_name NVARCHAR(255)
, referenced_entity_name NVARCHAR(255)
, join_clause NVARCHAR(MAX)
, [LEVEL] TINYINT
, lineage NVARCHAR(MAX)
)
DECLARE @viewname NVARCHAR(1000),
@count INT,        -- Current ID
@maxCount INT    -- Max ID of the temp table

-- Set the name of the top level view you want to detangle
SELECT @viewName = N'vw_stadium_tickets_current_football',
@count = 1
----------------------------------------------------------------------------
-- Seed the table with the root view, and the root view's referenced tables.
----------------------------------------------------------------------------
INSERT INTO #viewHierarchy
SELECT NULL parent_view_id
, 'dbo' referenced_schema_name
, @viewName referenced_entity_name
, NULL join_clause
, 0 [LEVEL]
, '/' lineage

INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, 1 [LEVEL]
, '/1/' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')

SELECT @maxCount = MAX(id)
FROM #viewHierarchy
----------------------------------------------------------------------------
-- Loop through the nested views.
----------------------------------------------------------------------------
WHILE (@count < @maxCount) -- While there are still rows to process...
BEGIN
SELECT @count = @count + 1

    -- Get the name of the current view (that we'd like references for)
SELECT @viewName = referenced_entity_name
FROM #viewHierarchy
WHERE id = @count

    -- If it's a view (not a table), insert referenced objects into temp table.
IF EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = 'v')
BEGIN
INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, NULL [LEVEL]
, '' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')
SELECT @maxCount = MAX(id)
FROM #viewHierarchy
END
END
--------------------------------------
--------------------------------------
WHILE EXISTS (SELECT 1 FROM #viewHierarchy WHERE [LEVEL] IS NULL)
UPDATE T
SET T.[Level] = P.[Level] + 1,
T.Lineage = P.Lineage + LTRIM(STR(T.parent_view_id,6,0)) + '/'
FROM #viewHierarchy AS T
INNER JOIN #viewHierarchy AS P ON (T.parent_view_id=P.ID)
WHERE P.[Level]>=0
AND P.Lineage IS NOT NULL
AND T.[Level] IS NULL
SELECT       parent.*
,child.id
,child.referenced_entity_name ChildName
FROM #viewHierarchy parent
RIGHT OUTER JOIN #viewHierarchy child ON child.parent_view_id = parent.id
ORDER BY parent.id, child.id

This generates a table that we join to itself to get a hierarchical view of parent and child nodes:

So far, this is useful in quickly getting an overall picture of how complicated a nested view is, how many times it hits a particular table, etc.  Like I said, this is the basics, and I’d like to do more. If you do more with it, drop a comment or email and let me know what you did! I’m always interested in seeing improvements and expansions.

Feel free to use this work in any way you like, but be kind and slap a link back here in the comments.

Happy days,

Jen

And as always, thanks to the Simple-Talk SQL Prettifier for its help today.