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's gravatar image

girouxlp
66127
accept rate: 0%

1

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

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%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
6.9k29101
accept rate: 31%

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...
Your answer
toggle preview

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

Question tags:

×102
×101
×78
×22

question asked: 17 Mar '16, 10:02

question was seen: 265 times

last updated: 22 Mar '16, 11:45