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
382182126
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
27.3k424585836

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.9k294446654
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:

×104
×28
×24

question asked: 05 Jan '10, 22:00

question was seen: 1,121 times

last updated: 31 May '11, 11:29