Get list of Functions and Procedures

[Former Member]

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

[Former Member · Accepted solution]

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!

[Former Member · Accepted solution]

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

[Breck_Carter · Accepted solution]

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

[VolkerBarth · Accepted solution]

...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).

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[VolkerBarth]

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.