How to know all the db's procedures

fernando guerrero · #1 (**permalink**) 08-23-11, 18:04

Hello

How can I know the name of all the procedures that exist in a specific database??

rgomezs · #2 (**permalink**) 08-25-11, 05:10

Hi Fernando.

You can do it by two ways:

1.- Use "dbaccess" utility: dbaccess -> Database -> Select -> "choose your database" -> Info -> Routine

2.- Use "dbschema" utility: From the propmpt shell type:
dbschema -q -f all -d yourdatabase output_filename

You´ll get a file named output_filename with the SQL to create all procedures.

BrentNZ · #3 (**permalink**) 09-20-11, 00:50

Hi,

The following should produce a list of all procedures in database your_database_name :

dbschema -d your_database_name | awk '/CREATE PROCEDURE/ {print substr($3,index($3,"(")-1)}'

Cheers,
Brent.

begooden-it · #4 (**permalink**) 09-25-11, 15:41

Hi Fernando,

just use dbschema with the right option:

Code:

dbschema -d databasename -f all

this will list you all the functions and stored procedures source code for your database.

If you just want the procedures names:

Code:

dbaccess databasename
Query/New/
type:
select procname from sysprocedures

and here you go
Eric

Internet Services

Web Development

Digital Marketing

Consumer Tech