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
This is very helpful. Thanks -Varma
ReplyDeleteI 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