SAP SQL Anywhere

Question

What SQL do I use to get a list of functions and a list of procedures from the database? I need to get two distinct lists. This SQL brings back both procedures and functions in one list:

select b.name + '.' + a.name, a.id, a.crdate, '', 0, ''
from sysobjects a, sysusers b
where a.type = 'P'
and a.uid = b.uid
order by b.name, a.name

Thanks,

Brad

Accepted Answer

5

SQLA: 10.0.1
This may not be the best solution, but I couldn't another way to determine one from the other.

select 
    su.user_name + '.' + sp.proc_name as Full_Name, 
    CASE substr(left(proc_defn,8), 8, 1) 
        WHEN 'p' THEN 'Procedure' 
        WHEN 'f' THEN 'Function' 
    END as Object_Type

from 
    sysprocedure sp, 
    sysuser su

where 
    sp.creator = su.user_id

order by 
    su.user_name, 
    sp.proc_name

Then, of course, you could restrict based on Object_Type.

Hope it helps!

permanent link

answered 05 Jan '10, 22:49

Calvin Allen
1.5k●23●26●38
accept rate: 25%

edited 06 Jan '10, 11:24

Breck Carter
32.5k●541●726●1050

I'll go with this for now. It works, that's all I need right now.

(05 Jan '10, 23:33) Brad Wery

It probably is the best solution. Sadly, the system catalog tables don't differentiate, with sysobject.object_type = 6 for both.

(06 Jan '10, 11:35) Breck Carter

...Which leads to the question, how the database server differentiantes between both types? Or is this done in a context-sensitive way, i.e. CALL myFunction(1) is somewhat different from SELECT myFunction(1)? (AFAIK, using functions quite like procedures - including INOUT parameters and the like - is possible in older SA versions, adding to the conclusion that both types are more similar than expected).

(06 Jan '10, 11:55) Volker Barth

Yeah, there are also calls to 'drop procedure' and 'drop function'. How do they work?

(06 Jan '10, 17:29) Calvin Allen

1

Nevermind, I just answered my own question. 'drop procedure' will drop functions, and 'drop function' will drop procedures. Strange implementation.

(06 Jan '10, 19:41) Calvin Allen

flat view

Answer 2

0

Just a backlink from a similar question:

Karim has shown a better approach (IMHO) here, based on querying sysprocparm:

For each function, that table contains an entry with parm_type = 4 for the function's return type - something that procedure do not have by design. So that should be a reliable way to differentiate them.

permanent link

answered 31 May '11, 11:28

Volker Barth
40.2k●361●549●822
accept rate: 34%

edited 31 May '11, 11:29

flat view

SAP SQL Anywhere

Get list of Functions and Procedures

Follow this question

Related questions