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

Introduction

A stored procedure can be called from another stored procedure as a 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 a simple script.

Using the Code

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