SQL Server 2005 -Seeing SPs executing inside SPs

Question

I am wondering if it is possible to see stored procs that are being executed by other stored procs in Sql Server Profiler, is it possible, and if so how?

Answer 1

If you profile using the SP::Starting event, you can see all the stored procedures that are executed. You want to make sure to get a couple of the profile columns included:

NestLevel - This shows the nesting level of SPs calling each other - so if Test calls EmbeddedTest then the SP::Starting event for Test wil show NestLevel=1 and the event for EmbeddedTest will show NestLevel=2

ObjectName - This shows the name of the stored procedure being executed

SPID - Session ID for the connection

So to find a calling sequence you need to look for an event with NestLevel greater then 1 and then look for the NestLevel=1 on the same SPID that preceeded the call. Of course this works for deeper nesting levels as well.