|
In Is there anyway to separately list functions and sp's without parsing their definitions? Version: ASA 9 |
|
I am sure there are several different answers to your question, so here is my suggestion. I believe the following query will list all functions within the database: select p.proc_name from sysprocedure p,sysprocparm pp where p.proc_id = pp.proc_id and pp.parm_type=4 As I said, there are probably several different answers to your question and I am sure others will provide their own solution. 2
Interesting approach... So the following would list all procedures: select p.proc_name from sysprocedure p except select p.proc_name from sysprocedure p,sysprocparm pp where p.proc_id = pp.proc_id and pp.parm_type = 4
(30 May '11, 11:37)
Volker Barth
Replies hidden
Comment Text Removed
The following might be more efficient: select distinct p.proc_name from sysprocedure p,sysprocparm pp where p.proc_id = pp.proc_id and pp.parm_type <> 4
(30 May '11, 11:55)
Karim Khamis
Yes, both queries work, thanks. I guess sysprocparm.parm_type=4 means the "procedure" has a return parameter.
(30 May '11, 11:56)
henginy
2
...but wouldn't it omit procedures that neither have any parameters nor a result clause, something like create proc MyProc() begin ... end;
(30 May '11, 12:08)
Volker Barth
Yes, you are absolutely correct Volker. Bad oversight on my part. Karim
(30 May '11, 12:23)
Karim Khamis
|
|
AFAIK, there's no official column in the system catalog to distinguish procedures and functions, and historically, they could be used somewhat interchangeable. This question leads to a workaround. EDIT: Well, obviously Karim's suggestion is the better one - almost an "official solution":) Thanks, interesting link.
(30 May '11, 11:57)
henginy
|
|
For v17 (starting with build 17.0.4.2000), you can now filter by the newly added "is_deterministic" column (which requires a database upgrade or rebuild): select * from sysprocedure where is_deterministic is not null returns stored functions and select * from sysprocedure where is_deterministic is null returns stored procedures. |