Monday, July 23, 2012

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


2 comments:

  1. This is very helpful. Thanks -Varma

    ReplyDelete
  2. I want whole list. E.g. SP1 calls SP2 ...SP2 calls SP3 ...SP3 calls SP4...when i provide SP4 then it should return SP4--> SP3-->SP2-->SP1

    ReplyDelete

All suggestions are welcome