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 |