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

Answer 1

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!