Is there a way to tell what stored procedure called another stored procedure in SQL Server?

user_11120287 0
Viewed 111

I am adding some debug code to a stored procedure that creates XML message. This stored procedure is called from many other stored procedures, and I want to see what stored procedures are calling that stored procedure that creates the XML.

Is there a system stored procedure that can provide this information?

For example, SP1 calls SP_XML. IN SP_XML, add code to tell me that SP1 is the caller.

Not sure if there is system stored procedure or a way to tell me this, even better would be if I can see the whole tree of stored procedure being called.

tsql sql-server
2 Answers

hope this clarify you.

select schema_name(o.schema_id) + '.' + o.name as [procedure],
       'is used by' as ref,
       schema_name(ref_o.schema_id) + '.' + ref_o.name as [object],
       ref_o.type_desc as object_type
from sys.objects o
join sys.sql_expression_dependencies dep
     on o.object_id = dep.referenced_id
join sys.objects ref_o
     on dep.referencing_id = ref_o.object_id
where o.type in ('P', 'X')
      and schema_name(o.schema_id) = 'dbo'  -- put schema name here
      and o.name = 'help'  -- put procedure name here
order by [object];
user_184440700

One piece of code I use to find a list of all stored procedures that reference is referenced is other procedures is:

WITH ProcsThatCallProcs (StoredProc, CodeID, calledproc) AS ( SELECT Code = SCHEMA_NAME(Objects.schema_id) + '.' + Objects.name , CodeID = Objects.Object_id , called_proc = ISNULL((SELECT DISTINCT STUFF((SELECT DISTINCT ', ' + ReferencedOjbjects.name FROM sys.sql_expression_dependencies Referenced INNER JOIN sys.objects ReferencedOjbjects ON Referenced.referencing_id = ReferencedOjbjects.object_id

                                                                                                            WHERE Referenced.referenced_id = Objects.object_id
                                                                                                            GROUP BY ReferencedOjbjects.name
                                                                                                            ORDER BY ', ' + ReferencedOjbjects.name
                                                                                                            FOR XML PATH(''))
                                                                                                       , 1,1, ''))
                                                            , '')
      FROM Sys.Objects Objects
      WHERE Objects.Type IN ('P', 'X')

) SELECT StoredProc , calledproc FROM ProcsThatCallProcs WHERE calledproc <> '' ORDER BY StoredProc

user_104576590
Related