Detangling Nested Views

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.

21 thoughts on “Detangling Nested Views”

  1. I can’t wait to play with this when I get the time. I am totally behind you with nested views….they are evil. Go write some SSRS reports for Microsoft Operations Manager and see how slowly they run. Then trace them to see what they call and you will find a nested view firestorm like you have never seen before…and all built in nicely to a Microsoft product. Granted they bought it off of someone else and did minimal changes before releasing it as their own, but what’s new.

    In the words of Chris Farley in Black Sheep….Nested views make me want to spike their heads onto the floor of a nightmare you can’t even imagine and dance with you in this exciting ring of fire unless they vacate the SQL premise far…and fast…now!

  2. Bob, glad to’ve brought you two together! :)

    Ryan – Cripes, man, you hate nested views NEARLY as much as I do! Let me know if you come up with something cool as a result, and I’m going to keep developing this in my “spare time”, too…

  3. What a great idea. I’d forgotten one of my nightmares from my ancient past was troubleshooting a hierarchy of MS Access views that processed a mainframe payroll report. Very unpleasant times indeed. I don’t think we have anything like this now in our SQL Server inventory, but if I find some I’ll test this.

  4. If you are using Windows Authentication in SQL server you might have to start caring a little about AD passwords. Go do a search for how LM and NTLM work and you will ask your AD admin to change the domain policy right after changing your password to something longer.

  5. Cool script. Added functions. Been better.

    — 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’)
    OR EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = ‘TF’)
    OR EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = ‘FN’)

  6. Sorry, but this query doesn’t work, it gets stuck in an infinite loop, repeats itself, and keeps adding the same records to the table. I tried it on a simple three-level view.

  7. HOLY MARY MOTHER OF GOD! I have hit the jackpot!

    I have a “Web App” developer who thinks she is a SQL Developer because she can force SQL engine to sort a view by nesting 6 views with UNION and CROSS APPLY and such.

    THANK YOU SO MUCH for this code so I can actually show “the powers that be” the EVIL THAT DEVELOPERS DO inside of SQL.

    1. I am so pleased this will be useful for you! I’ve meant (for 4 years now) to do more with this, but even in its beta form, it’s been useful to me too. And sometimes, for reasons similar to yours.

      Good fortune, man. Oddly, I’m just about to publish another blog post (tomorrow) about nested views. Great timing.

  8. Robert, tell the half baked developer that they can easily sort views by using the TOP 100% if they want to be a dangus, no nesting required.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>