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
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
), 2, 8000) AS NestedProcedures
FROM sys.procedures procs )InnerTab
WHERE NestedProcedures IS NOT NULL