Procedure dependency graph

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

asked 17 Mar '16, 10:02

girouxlp
81●3●3●8
accept rate: 0%

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.

(22 Mar '16, 08:57) Breck Carter

Replies hidden

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.

(22 Mar '16, 11:31) Volker Barth

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 17 Mar '16, 18:27

Nick Elson S...
7.3k●35●107
accept rate: 32%

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.

(22 Mar '16, 08:21) girouxlp

Replies hidden

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.

(22 Mar '16, 08:44) Volker Barth

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.

(22 Mar '16, 11:45) Nick Elson S...

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sybase-central ×128
stored-procedure ×125
documentation ×93
external-call ×23

question asked: 17 Mar '16, 10:02

question was seen: 2,110 times

last updated: 22 Mar '16, 11:45

SAP SQL Anywhere

Procedure dependency graph

Follow this question

Related questions