SQL – Procedure and Function Caller Hierarchy

Copy

-- Create tables to be the leaf nodes of the hierarchical tree.
CREATE TABLE Table_A (
     ID INT IDENTITY NOT NULL PRIMARY KEY
    ,Name varchar(10) NOT NULL DEFAULT 'A'
)
GO

CREATE TABLE Table_B (
     ID INT IDENTITY NOT NULL PRIMARY KEY
    ,Name varchar(10) NOT NULL DEFAULT 'B'
)
GO

CREATE TABLE Table_C (
     ID INT IDENTITY NOT NULL PRIMARY KEY
    ,Name varchar(10) NOT NULL DEFAULT 'C'
)
GO

-- Create functions to be the intermediate levels in the hierarchical tree.
CREATE FUNCTION Table_A_Count()
RETURNS INT
AS
BEGIN 
    RETURN (
        SELECT COUNT(1)
        FROM Table_A
    )
END
GO

CREATE FUNCTION Table_B_Count()
RETURNS INT
AS
BEGIN 
    RETURN (
        SELECT COUNT(1)
        FROM Table_B
    )
END
GO

CREATE FUNCTION Table_C_Count()
RETURNS INT
AS
BEGIN 
    RETURN (
        SELECT COUNT(1)
        FROM Table_C
    )
END
GO

-- Create stored procedure to be the root of the hierarchical tree.
CREATE PROCEDURE dbo.Table_Count
AS
BEGIN
    SELECT [Table] = 'A', [Row Count] = dbo.Table_A_Count()
    UNION
    SELECT [Table] = 'B', [Row Count] = dbo.Table_B_Count()
    UNION
    SELECT [Table] = 'C', [Row Count] = dbo.Table_C_Count()
END
GO

Copy

-- ----------------------------------------------------------------------------
-- Create a temp table of distinct caller/callee relationships.  DISTINCT is
-- used since sys.sql_expression_dependencies may have multiple rows for a 
-- relationship.
-- The columns are also renamed as nouns instead of verbs.
DECLARE @Dependencies TABLE (
     CallerID INT
    ,CallerName nvarchar(257)
    ,CallerType char(2)
    ,CallerTypeName nvarchar(128)
    ,CalleeID INT
    ,CalleeName nvarchar(257)
    ,CalleeType char(2)
    ,CalleeTypeName nvarchar(128)
)
INSERT INTO @Dependencies (
     CallerID
    ,CallerName
    ,CallerType
    ,CallerTypeName
    ,CalleeID
    ,CalleeName
    ,CalleeType
    ,CalleeTypeName
)
SELECT DISTINCT 
     [CallerID] = referencing_id
    ,[CallerName] = CAST(callerSchema.name + '.' + caller.name AS nvarchar(257))
    ,[CallerType] = caller.type
    ,[CallerTypeName] = caller.type_desc
    ,[CalleeID] = referenced_id
    ,[CalleeName] = CAST(calleeSchema.name + '.' + callee.name AS nvarchar(257))
    ,[CalleeType] = callee.type
    ,[CalleeTypeName] = callee.type_desc
FROM sys.sql_expression_dependencies depends
JOIN sys.objects caller ON caller.object_id = depends.referencing_id
JOIN sys.schemas callerSchema on callerSchema.schema_id = caller.schema_id
JOIN sys.objects callee ON callee.object_id = depends.referenced_id
JOIN sys.schemas calleeSchema on calleeSchema.schema_id = callee.schema_id


-- ----------------------------------------------------------------------------
-- Get a hierarchical list of stored procedures, functions and tables used by 
-- the specified stored procedure or function.
;WITH HierarchyCTE
AS (
    -- Base Case: Get the root level of caller and callee relationship.
    SELECT --
         [CallerID]
        ,[CallerName]
        ,[CallerType]
        ,[CallerTypeName]
        ,[CalleeID]
        ,[CalleeName]
        ,[CalleeType]
        ,[CalleeTypeName]
        ,[Path] = CAST(CallerName + ' » ' + CalleeName AS nvarchar(MAX))
        ,[Depth] = 0
        ,[Root] = CallerName
        ,[Leaf] = CalleeName
        ,[Count] = 1
    FROM @Dependencies
    WHERE CallerID = OBJECT_ID(N'dbo.Table_Count')			-- < Enter stored procedure or function here.

    UNION ALL
    
    -- Recursive Case: Recursively call HierarchyCTE to get the callees for the callee.
    SELECT --
         depends.[CallerID]
        ,depends.[CallerName]
        ,depends.[CallerType]
        ,depends.[CallerTypeName]
        ,depends.[CalleeID]
        ,depends.[CalleeName]
        ,depends.[CalleeType]
        ,depends.[CalleeTypeName]
        ,[Path] = CAST(cte.Path + ' » ' + depends.CalleeName AS nvarchar(MAX))
        ,[Depth] = cte.Depth + 1
        ,[Root] = cte.Root
        ,[Leaf] = depends.CalleeName
        ,[Count] = COUNT(depends.CallerID) OVER (PARTITION BY depends.CallerID)
    FROM HierarchyCTE cte
    JOIN @Dependencies depends ON depends.CallerID = cte.CalleeID
    WHERE depends.CallerID != depends.CalleeID -- Avoid recursive CTE's
         AND cte.Count = 1		-- Prevent adding a callee more than once.
    )
SELECT *
INTO #HierarchyCTE
FROM HierarchyCTE

-- ------------------------------------
SELECT --  Complete hierarchical list.
	 Depth, CallerName, CallerTypeName, CalleeName, CalleeTypeName, Path, Root, Leaf
FROM #HierarchyCTE
ORDER BY
	Depth, CallerName, CalleeName

-- ------------------------------------
SELECT -- Just the tables involved in the query.
    DISTINCT [Root], [Leaf]
FROM #HierarchyCTE
WHERE [CalleeType] = 'U'
ORDER BY 
	Root, Leaf

-- ------------------------------------
SELECT -- Just the tables involved in the query and each path to that table.
    [Root], [Leaf], [Path]
FROM #HierarchyCTE
WHERE [CalleeType] = 'U'
ORDER BY 
	Root, Leaf

IF OBJECT_ID('tempdb..#HierarchyCTE') IS NOT NULL DROP TABLE #HierarchyCTE

Depth	CallerName	CallerTypeName	CalleeName	CalleeTypeName	Path	Root	Leaf
0	dbo.Table_Count	SQL_STORED_PROCEDURE	dbo.Table_A_Count	SQL_SCALAR_FUNCTION	dbo.Table_Count » dbo.Table_A_Count	dbo.Table_Count	dbo.Table_A_Count
0	dbo.Table_Count	SQL_STORED_PROCEDURE	dbo.Table_B_Count	SQL_SCALAR_FUNCTION	dbo.Table_Count » dbo.Table_B_Count	dbo.Table_Count	dbo.Table_B_Count
0	dbo.Table_Count	SQL_STORED_PROCEDURE	dbo.Table_C_Count	SQL_SCALAR_FUNCTION	dbo.Table_Count » dbo.Table_C_Count	dbo.Table_Count	dbo.Table_C_Count
1	dbo.Table_C_Count	SQL_SCALAR_FUNCTION	dbo.Table_C	USER_TABLE	dbo.Table_Count » dbo.Table_C_Count » dbo.Table_C	dbo.Table_Count	dbo.Table_C
1	dbo.Table_B_Count	SQL_SCALAR_FUNCTION	dbo.Table_B	USER_TABLE	dbo.Table_Count » dbo.Table_B_Count » dbo.Table_B	dbo.Table_Count	dbo.Table_B
1	dbo.Table_A_Count	SQL_SCALAR_FUNCTION	dbo.Table_A	USER_TABLE	dbo.Table_Count » dbo.Table_A_Count » dbo.Table_A	dbo.Table_Count	dbo.Table_A

Root	Leaf
dbo.Table_Count	dbo.Table_A
dbo.Table_Count	dbo.Table_B
dbo.Table_Count	dbo.Table_C

Root	Leaf	Path
dbo.Table_Count	dbo.Table_C	dbo.Table_Count » dbo.Table_C_Count » dbo.Table_C
dbo.Table_Count	dbo.Table_B	dbo.Table_Count » dbo.Table_B_Count » dbo.Table_B
dbo.Table_Count	dbo.Table_A	dbo.Table_Count » dbo.Table_A_Count » dbo.Table_A