Dependency Map of the Nested Stored Procedures using DMV

One of my customers, had a requirement to identify the call graph (or other words dependency map)  for the nested stored procedures. While with SQL 2008, two new DMVs viz  sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities  to identify the referencing and referenced objects by the stored procedures within the database.

However, the DMV gives the information of the immediate stored procedure referenced by the stored procedures, but if there is multiple level of nesting of stored procedure, it is not available out of box.

I have developed a following script which uses recursive CTE to develop dependency map of the nested stored procedure.

DECLARE @procname varchar(30)
SET @procname='dbo.Proc2'

;WITH CTE([DB],[OBJ],[SCH],[lvl])
AS
(
SELECT referenced_database_name AS [DB],referenced_entity_name AS [OBJ],
referenced_schema_name AS [SCH],0 AS [lvl]
FROM sys.dm_sql_referenced_entities(@procname, 'OBJECT') 
INNER JOIN sys.objects as o on o.object_id=OBJECT_ID(referenced_entity_name)
WHERE o.type IN ('P','FN','IF','TF')

UNION ALL

SELECT referenced_database_name AS [DB],referenced_entity_name AS [OBJ],
referenced_schema_name AS [SCH],[lvl]+1 as [lvl]
FROM CTE as c CROSS APPLY
sys.dm_sql_referenced_entities(c.SCH+'.'+c.OBJ, 'OBJECT') as ref
INNER JOIN sys.objects as o on o.object_id=OBJECT_ID(referenced_entity_name)
WHERE o.type IN ('P','FN','IF','TF') and ref.referenced_entity_name NOT IN (c.OBJ)  -- Exit Condition
)

SELECT * FROM CTE

--This is my test case where I tried it on 

/*
CREATE TABLE Table1 (a int, b int, c char(2));
GO
CREATE TABLE Table2(c1 int, c2 int)
GO

CREATE PROCEDURE dbo.Proc1 AS
    SELECT a, b, c FROM dbo.Table1;
    SELECT c1, c2 FROM dbo.Table2;
GO

CREATE PROC dbo.Proc5
AS
BEGIN
SELECT a, b, c FROM dbo.Table1;
END
GO

CREATE PROC dbo.Proc3
AS
BEGIN
SELECT c1, c2 FROM dbo.Table2;
EXEC Proc5
END
GO

CREATE PROC dbo.Proc4
AS
BEGIN
SELECT a, b, c FROM dbo.Table1;
EXEC dbo.Proc5
END
GO

CREATE PROC dbo.proc2 
AS
BEGIN
EXEC dbo.Proc1
EXEC dbo.Proc3
EXEC dbo.Proc4
END
GO

DROP PROCEDURE dbo.Proc1
DROP PROCEDURE dbo.Proc2
DROP PROCEDURE dbo.Proc3
DROP PROCEDURE dbo.Proc4
DROP PROCEDURE dbo.Proc5
DROP TABLE Table1
DROP TABLE Table2

*/

Hope it helps !!!

Parikshit Savjani
Premier Field Engineer