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.
answered 30 May '11, 11:29
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.
Well, obviously Karim's suggestion is the better one - almost an "official solution":)
select * from sysprocedure where is_deterministic is not null
returns stored functions and
select * from sysprocedure where is_deterministic is null
returns stored procedures.
answered 31 Mar, 03:13