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