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],
           CAST(
             QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@object_name))) + '.' + 
             QUOTENAME(OBJECT_NAME(OBJECT_ID(@object_name)))
             as sysname) as [escaped_name],
           [type_desc],
           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)
 
    UNION ALL
 
    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),
           o.[type_desc],
           o.[object_id],
           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)) + '%'
 
     UNION ALL
 
     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),
            'SQL_TRIGGER',
            t.[object_id],
            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

9 Comments »

  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

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress