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
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.