Procedure dependency graph

[Breck_Carter]

Hi, I’m trying to build a procedures dependency graph to identify changes in procedures call that affect a specific user. Using Sybase central, it’s possible to generate database documentation (Tool/SQL Anywhere 16/ Generate Database Documentation). The fields “This Procedure is Called By“ and “This Procedure Calls“ generate the information needed. I’m looking for a way to generate the information from a procedure or from an external program to avoid human interaction. Is it possible?

Thanks for the help, LP

[Breck_Carter]

You will not find any semantic information about who-calls-who in the system catalog tables. That's because "Normally, the optimizer selects an execution plan for a query every time the query is executed."

In that Help topic, "query" means pretty much any SQL statement. For example, a procedure named in a CALL statement does not even have to exist when the calling procedure is created... that's one of the hallmarks of SQL Anywhere.

That Help topic does discuss "plan caching" but that process is optional, is dynamic, is performed on a per-connection basis, and the results are not made visible in the system catalog.

Nick's suggestion to "parse those directly" is the only way to achieve what you want.

[VolkerBarth]

Furthermore conditional execution (IF/CASE statements, IF/CASE expressions, prodedure/function calls as part of query blocks, LATERAL calls, ...) will make if difficult to tell whether a certain inner procedure/function will be called at all in an outer procedure/function under particular conditions. Say, there's something like

...
if (select count(*) from myTable where x > y) > 0 then
   call ThatOtherProc();
end if;

might mean procedure "ThatOtherProc" could be called here but in reality that branch might never be reached.

[Former Member · Accepted solution]

The logic that performs this little bit of magic is entirely contained in the SQL Central wizard for this function. Other than querying the system for the metadata there are no functions or procedures from the database involved in doing that.

You might be able to export the objects as seperate HTML files (1 per) and to parse those directly ... though I don't suspect that is as convenient as you would like.

[Former Member · Accepted solution]

Thanks for the reply. Not the answer I was hoping for... Any idea how to use metadata to achieve a procedure's dependency graph? Everything is available for tables and views, but I couldn't find where procedures metadata lives.

[VolkerBarth · Accepted solution]

There are several views in the system catalog that deal with procedures (and functions likewise) like SYSPROCEDURE, SYSPROCPARM and SYSPROCPERM.

For the procedures's source code (containing possible calls of other procedures/functions), you will have a look at the SYSPROCEDURE "proc_defn" and "source" columns.

[Former Member · Accepted solution]

If you are up to parsing SQL yourself, then you could work from the stored definitions. That is basically what SQL Central is doing here.

We currently don't track dependancies for procedures like we added for views so you don't have attributes or properties tracked in the metadata.