how would you go about automating finding all the procs that a proc calls, along with their text?

Question

Say I have a stored procedure, ProcA. It calls ProcB some of the time, ProcC all of the time and ProcD if it hits an error. ProcB calls ProcE every time. ProcC calls ProcF if it hits an error.

How could I automate getting the text of ProcA along with the text of all the procs that it calls and regress all the way down the tree (A down to F)? This would help a lot with finding errors in complex sql procs.

My first thought here is get the text of ProcA, regex through it and find any calls to other procs, wash rinse repeat, at the end spit out a text (file or to UI) that looks like this:

ProcA definition
... 
ProcB definition
...
... 
ProcF definition

But I'm open to suggestion, perhaps there's an easier way. If there's a tool that does this already lemme know. I have no idea what to put into google on this one.

Answer 1

In SQL Server you have sys.sysdepends and sys.syscomments.

INFORMATION_SCHEMA.ROUTINES can be useful in the general SQL case, but it has a limit to the size of the text returned on SQL Server, so I avoid it.

Answer 2

this is highly database dependent, because the best way (if possible) would be to query the database's internal tables.

Answer 3

If you're using MS SQL in SQL 2000 Enterprise Manager right click on your stored procedure > All Tasks > Display Dependencies. In SQL 2005 Management Studio, right click on your stored procedure > View Dependencies.

It won't show you any code for the other objects that this proc id dependent on, but it will list the objects which you can then "wash, rinse, repeat"