SQL – Procedure and Function Callee 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 and functions used by 
-- the specified table, 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(CalleeName + ' « ' +  CallerName AS NVARCHAR(MAX))
        ,[Depth] = 0
        ,[Root] = CalleeName
        ,[Leaf] = CallerName
        ,[Count] = 1
    FROM @Dependencies depends
    WHERE CalleeID = OBJECT_ID(N'dbo.Table_A')
    
    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.CallerName AS NVARCHAR(MAX))
        ,[Depth] = cte.Depth + 1
        ,[Root] = cte.Root
        ,[Leaf] = depends.CallerName
        ,[Count] = COUNT(depends.CalleeID) OVER (PARTITION BY depends.CalleeID)

    FROM HierarchyCTE cte
    JOIN @Dependencies depends ON depends.CalleeID = cte.CallerID
    WHERE depends.CallerID != depends.CalleeID -- Avoid recursive CTE's
         AND cte.Count = 1        -- Prevent adding a callee more than once.
    )
SELECT Depth, CalleeName, CalleeTypeName, CallerName, CallerTypeName, Path, Root, Leaf
FROM HierarchyCTE
ORDER BY 
    Depth, CalleeName, CallerName

Depth	CalleeName	CalleeType	CallerName	CallerType	Path	Root	Leaf
0	dbo.Table_A	USER_TABLE	dbo.Table_A_Count	SQL_SCALAR_FUNCTION	dbo.Table_A « dbo.Table_A_Count	dbo.Table_A	dbo.Table_A_Count
1	dbo.Table_A_Count	SQL_SCALAR_FUNCTION	dbo.Table_Count	SQL_STORED_PROCEDURE	dbo.Table_A « dbo.Table_A_Count « dbo.Table_Count	dbo.Table_A	dbo.Table_Count