The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 05 Jan '10, 22:00

Brad%20Wery's gravatar image

Brad Wery
382182026
accept rate: 0%


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%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

edited 06 Jan '10, 11:24

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824

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

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%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 31 May '11, 11:29

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×100
×26
×24

question asked: 05 Jan '10, 22:00

question was seen: 1,063 times

last updated: 31 May '11, 11:29