Michael J. Swart

October 26, 2018

Uncovering Hidden Complexity

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:15 pm

The other day, Erin Stellato asked a question on twitter about the value of nested SPs. Here’s how I weighed in:

Hidden complexity has given me many problems in the past. SQL Server really really likes things simple and so it’s nice to be able to uncover that complexity. Andy Yun has tackled this problem for nested views with his sp_helpexpandview.

Here’s what I came up with for nested anything. It helps unravel a tree of dependencies based on information found in sys.triggers and sys.dm_sql_referenced_entities. With it, you can see what’s involved when interacting with objects. Here’s what things look like for Sales.SalesOrderDetail in AdventureWorks2014. A lot of the resulting rows can be ignored, but there can be surprises in there too.

A lot in there

DECLARE @object_name SYSNAME = 'Sales.SalesOrderDetail';
WITH dependencies AS
    SELECT @object_name AS [object_name],
             QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@object_name))) + '.' + 
             as sysname) as [escaped_name],
           object_id(@object_name) AS [object_id],
           1 AS is_updated,
           CAST('/' + CAST(object_id(@object_name) % 10000 as VARCHAR(30)) + '/' AS hierarchyid) as tree,
           0 as trigger_parent_id
      FROM sys.objects 
     WHERE object_id = object_id(@object_name)
    SELECT CAST(OBJECT_SCHEMA_NAME(o.[object_id]) + '.' + OBJECT_NAME(o.[object_id]) as sysname),
           CAST(QUOTENAME(OBJECT_SCHEMA_NAME(o.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(o.[object_id])) as sysname),
           CASE o.[type] when 'U' then re.is_updated else 1 end,
           CAST(d.tree.ToString() + CAST(o.[object_id] % 10000 as VARCHAR(30)) + '/' AS hierarchyid),
           0 as trigger_parent_id
      FROM dependencies d
     CROSS APPLY sys.dm_sql_referenced_entities(d.[escaped_name], default) re
      JOIN sys.objects o
           ON o.object_id = isnull(re.referenced_id, object_id(ISNULL(re.referenced_schema_name,'dbo') + '.' + re.referenced_entity_name))
     WHERE tree.GetLevel() < 10
       AND re.referenced_minor_id = 0
       AND o.[object_id] <> d.trigger_parent_id
       AND CAST(d.tree.ToString() as varchar(1000)) not like '%' + CAST(o.[object_id] % 10000 as varchar(1000)) + '%'
     SELECT CAST(OBJECT_SCHEMA_NAME(t.[object_id]) + '.' + OBJECT_NAME(t.[object_id]) as sysname),
            CAST(QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(t.[object_id])) as sysname),
            0 AS is_updated,
            CAST(d.tree.ToString() + CAST(t.object_id % 10000 as VARCHAR(30)) + '/' AS hierarchyid),
            t.parent_id as trigger_parent_id
       FROM dependencies d
       JOIN sys.triggers t
            ON d.[object_id] = t.parent_id
      WHERE d.is_updated = 1
        AND tree.GetLevel() < 10
        AND CAST(d.tree.ToString() as varchar(1000)) not like '%' + cast(t.[object_id] % 10000 as varchar(1000)) + '%'
SELECT replicate('—', tree.GetLevel() - 1) + ' ' + [object_name], 
       [type_desc] as [type],
       tree.ToString() as dependencies       
  FROM dependencies
 ORDER BY tree


  1. Howdy sir! Ooo, neat – one really tiny tweak: the top lines have the declares twice. (Looks like a copy/paste from testing, maybe.)

    Comment by Brent Ozar — October 26, 2018 @ 1:26 pm

  2. It was indeed a copy paste error from testing. Thanks!

    Comment by Michael J. Swart — October 26, 2018 @ 1:55 pm

  3. I don’t understand the results, what are those numbers under “dependencies” column? Thank you

    Comment by Francesco Mantovani — October 28, 2018 @ 3:53 pm

  4. Hi Francesco,
    The numbers represent the hierarchy of object ids. They’re not super important for interpreting complexity but I find them useful when the indents are hard to see.
    Think of it this way. If the objects were files in a file system then these would be the paths to that file.

    Comment by Michael J. Swart — October 28, 2018 @ 4:03 pm

  5. […] Michael J. Swart helps us uncover hidden complexity in database objects: […]

    Pingback by Uncovering Complexity In SQL Objects – Curated SQL — October 29, 2018 @ 7:59 am

  6. Michael,

    This has come at the most appropriate time. I’ve turned it into a system stored procedure with the object name as the input.

    Just been tracking a major problem and this has made some of the investigation so much easier. Now I have to wonder how a particular sp ever runs and works.

    Comment by Chris Wood — October 31, 2018 @ 4:36 pm

  7. I’m glad to hear it Chris! It’s funny how these coincidences can pop up.

    Comment by Michael J. Swart — October 31, 2018 @ 4:46 pm

  8. Nice indeed! Thanks a bunch. I tweaked it a little by adding a space between the hyphens, making it easier to see the nesting levels.

    Comment by Magnus H — November 12, 2018 @ 2:41 am

  9. This process will definitely go into my toolbox; thanks for the work in putting it together. This led me into researching “hierarchyid” because it was new to me, so I learned a lot today.

    I made a slight revision to add to the hierarchy by using ROW_NUMBER() OVER (object_name) so that the child items would come out sorted by name rather than by object_id.

    Comment by Jonathan Fahey — November 12, 2018 @ 3:42 pm

  10. Don’t know why, but it doesn’t give me a dependencytree for a specific table in one of my databases. The table has a lot of dependencies but no triggers (3 table dependencies by FK and 8 SP dependencies at first level). SQL2012

    Comment by Wilfred van dijk — April 5, 2019 @ 4:03 am

  11. Hi Wilfred,
    The dependency tree only goes one way. So if procedure A inserts into Table B which has a Trigger C which updates table D, then the dependency can be described like this:
    A => B => C => D
    In this case, if you set the parameter name @ObjectName = 'A' you would see the dependency tree. But if you enter @ObjectName = 'D' you wouldn’t see a dependency tree.

    I wrote the script to answer “If I run this procedure, what tables may be read or written to?”

    Comment by Michael J. Swart — April 5, 2019 @ 8:37 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress