Have Geek Brain

How would you decide to automate the search for all processes that proc handles, along with your text?

Let's say I have a stored procedure, ProcA. It occasionally calls ProcB, ProcC all the time and ProcD if it gets into an error. ProcB calls ProcE every time. ProcC calls ProcF if it fails.

How can I automate getting the ProcA text along with the text of all the processing it calls and regresses all the way down the tree (A to F)? It would help a lot to find errors in complex sql processes.

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

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

But I'm open to suggestion, maybe there is an easier way. If there is a tool that knows about this already lemme. I have no idea what to put into google on this one.

0


a source to share


3 answers


In SQL Server, you have sys.sysdepends

and sys.syscomments

.



INFORMATION_SCHEMA.ROUTINES

might be useful in general SQL, but it has a limit on the size of text returned to SQL Server, so I avoid that.

+2


a source


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



0


a source


If you are using MS SQL in SQL 2000 Enterprise Manager, right click on the stored procedure> All Tasks> Display Dependencies. In SQL 2005 Management Studio, right click on Stored Procedure> View Dependencies.

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

0


a source






More articles:


All Articles