SQL Server Scripts: Get All Nested Stored Procedures List (Procedures with dependent Procedures)

A stored procedure can be called from another stored procedure as nested stored procedure. Recently on production server, we were asked for all stored procedures in which other stored procedures are called as nested. Here is simple script.

SELECT  * FROM (SELECT  NAME AS ProcedureName, SUBSTRING(( SELECT  ', ' + OBJDEP.NAME

FROM    sysdepends

        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID

        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID

WHERE obj.type = 'P'  

AND Objdep.type = 'P'

AND sysdepends.id = procs.object_id

ORDER BY OBJ.name

FOR

XML PATH('')

), 2, 8000) AS NestedProcedures

FROM sys.procedures  procs )InnerTab

WHERE NestedProcedures IS NOT NULL