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.

22 thoughts on “Detangling Nested Views

  1. Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » Detangling Nested Views -- Topsy.com

  2. Ryan Adams

    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!

  3. Jen McCown Post author

    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…

  4. Kevin Karns

    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.

    1. Jen McCown Post author

      Heh, sorry. I could sit down and explain it, but you care about that like I care about AD password hashing algorithms.

  5. Ryan Adams

    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.

  6. Pingback: SQL Awesomesauce » Blog Archive » 24 Hours of PASS Spring 2011 Code Sins

  7. Nate Hughes

    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’)

  8. Pingback: T-SQL Tuesday #21: Piling it on... | Matt Velic

  9. Kurt

    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.

    1. Jen McCown Post author

      I haven’t run into this before…could I get an obfuscated version of your objects, so I can pick apart this behavior and fix it in my script?

  10. Pingback: From the Vault: Detangling Nested Views | The MidnightDBA Star-Times

  11. Pingback: Tools and techniques for untangling monster views (nested views) | Question and Answer

  12. Robert

    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. Jen McCown Post author

      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.

  13. Pingback: I have hit the jackpot! | Padre's SQL Resort

  14. ck

    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.

  15. Pingback: Why don’t they make the optimizer faster? (An essay on nested views) | The MidnightDBA Star-Times

  16. Pingback: Is nested view a good database design? | XL-UAT

Comments are closed.