Find the complete call tree for a stored procedure

Linchi Shea — Fri, 23 Oct 2009 03:11:00 GMT

Would it be nice to print out the complete call tree of a stored procedure? By complete call tree, I mean the following:

· At the very top level, the call tree should identify all the procedures that are called directly.

· For any given level, the next level of the call tree should identify all the procedures called by the procedures at the current level.

· If a procedure calls another procedure on a different server via a linked server, that call should be identified on the call tree.

· The leaf level identifies the procedures that do not call any other procedure.

In other words, the procedure call tree should recursively identify all the dependent procedures that are called either directly or indirectly regardless whether they are on the same instance or a different instance.

I don’t know whether there is any tool out there that would print out such a procedure call tree. But this is not a trivial task if you want to be 100% complete. For instance, if another procedure is called through a piece of dynamic SQL, all bets are off. In that case, what is called will not be known until the run time when the dynamic SQL is executed. That is beyond the reach of any static code analysis.

Another difficulty is that there is no rigorous T-SQL parser that is publicly available you can use to shred the code of a stored procedure to find all the stored procedure calls. Using a rigorous and efficient T-SQL parser would have been the ideal way to accomplish this task.

Fortunately, as long as we are not talking about looking for all the dependencies including called procedures, views, tables, and so on, we can manage to get the procedure call tree with a little bit of regular expressions on the fact a stored procedure is always called with an EXECUTE statement. Although there are syntactic variations, it is rather straightforward to single out all the EXECUTE statements in a piece of T-SQL code, and then parse for the stored procedure names being called.

The attached Perl script does exactly that. If you want to find out what are being called by a procedure, say, p_MyGreatProc in database myDB on server NYSQL, you can run the Perl script as follows:

cmd>perl spCallTree.pl NYSQL.myDB.myOwner.p_Foo > callTree.log

Yes, the script is written to demand a four-part procedure name on the command line on purpose so as to remove any ambiguity, and all the stored procedures in the output are presented in the four-part format for the same reason.

An example of the logged output callTree.log may look like the following:

NYSQL.myDB.myOwner.p_Foo =>

{

'NYSQL.db1.dbo.p_Foo1' =>

{

'NYSQL.db2.dbo.p_Foo2' =>

{

'PASQL.db1.dbo.p_Foo3' =>

{

'CASQL.db2.dbo.sp_executesql' => undef

'NJSQL.db3.dbo.sp_executesql' => undef

'NYSQL.db.dbo.sp_executesql' => undef

'NYSQL.db1.dbo.p_Foo4' =>

{

'NYSQL.db2.dbo.p_Foo5' => undef,

'NJSQL.db2.dbo.p_Foo6' => undef

}

};

In this example, p_Foo calls several procedures directly or indirectly across a bunch of servers, including NYSQL, NJSQL, PASQL, and CASQL, and undef signifies that the procedure does not call any other procedure. So for instance, in the above output, NYSQL.myDB.myOwner.p_Foo calls NYSQL.db1.dbo.p_Foo1, which in turn calls NYSQL.db2.dbo.p_Foo2, which in turn calls PASQL.db1.dbo.p_Foo3, which in turns calls CASQL.db2.dbo.sp_executesql.

In the case of sp_executesql, more procedures may be called inside the dynamic SQL string executed by sp_executesql. But since we can’t evaluate the string, the script does not look further, and simply points it to undef. In case of EXECUTE on a dynamic SQL string variable, the output will simply identify the variable just in case you want to examine it further.

The Perl script callTree.pl finds out the call tree by scanning the code of the top most procedure for any EXECUTE statement after it has removed all the comments and quoted strings. Well, you would not want to include any reference to EXECUTE mentioned in a comment block, right? For each EXECUTE statement, it parses for the name of the procedure being called, including all the four parts (if necessary). It calls sp_helptext to get the code for the called procedure, and then scans the code for any EXECUTE statement. And this continues recursively until no more procedures are called.

I wrote this script fairly quickly as a throw-away script, but have used it many times. I can’t claim that it’s bug free, but so far has worked fine. You do need to have Perl on your machine to run the script. I’ve been using it with Perl v5.10.0, binary release 1003. You can download the latest release of ActivePerl from ActiveState.

If you need to debug a production issue that involves a procedure makes nested procedure calls with some of the calls going across liked servers, you would appreciate the need to see the complete call tree, and therefore, the utility of this little Perl script.

I have seen a fair amount of cross-server nested procedure calls, and they are simply nasty. I definitely have better things to do than manually find out which calls which and where. I’m sure you too. So hopefully you’ll find this little script useful.

And if you have a better or a different way of printing out the complete call tree for a procedure, let me know.

Search results matching tags 'Tools' and 'Scripting'

Find the complete call tree for a stored procedure