The other day, Erin Stellato asked a question on twitter about the value of nested SPs. Here’s how I weighed in:
I’m not a fan of nested anything. Too much hidden complexity. Code reusability leads to queries that are jack of all trades, master of none.
“Don’t repeat yourself” doesn’t work as well in SQL as it does in other code.— Michael J Swart (@MJSwart) October 19, 2018
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.
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 |
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
It was indeed a copy paste error from testing. Thanks!
Comment by Michael J. Swart — October 26, 2018 @ 1:55 pm
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
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
[…] 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
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
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
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
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
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
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