The SHOW PROCEDURE command

Use the SHOW PROCEDURE command to display information about one or more stored procedures. The command checks your user account privileges to ensure that you are permitted to see information about the procedures.

Synopsis

SHOW PROCEDURE [ALL | <name>] [VERBOSE]

Inputs

The SHOW PROCEDURE command takes the following inputs:
Table 1. SHOW PROCEDURE inputs
Input Description
ALL Show information about all the stored procedures defined in the database. This is the default.
name Show information about one or more stored procedures defined in the database. You can specify a partial name. The command displays information for all the procedures with names that begin with the specified characters. You cannot specify a full signature.
VERBOSE Display detailed information about the stored procedure.

Outputs

The SHOW PROCEDURE command has the following output:
Table 2. SHOW PROCEDURE output
Output Description
error found "(" (at char num) syntax error, unexpected '(', expecting $end The message that the system returns if you specify a full signature, for example: 
show procedure returntwo();

Description

The SHOW PROCEDURE command has the following characteristics:
Privileges required
Any user can run the SHOW PROCEDURE command. To see information about procedures in the output, you must be the admin user, own one or more procedures, own the current database or the schema on a system that supports multiple schemas, or have object privileges (such as Execute, List, Alter, or Drop) on one or more procedures or the Procedure object class.
Common tasks
Use the SHOW PROCEDURE command to display one or all stored procedures in a database.

Usage

To show the sample stored procedure named returntwo, use the following command: 
   MYDB.SCHEMA(ADMIN)=> SHOW PROCEDURE returntwo;
          RESULT          | PROCEDURE | BUILTIN |  ARGUMENTS
--------------------------+-----------+---------+-------------
 REFTABLE(MYDB.USER.TBL)  | RETURNTWO | f       | (TIMESTAMP)
(1 row)
To show verbose information for the sample stored procedure named returntwo, use the following command. The sample output is formatted to fit the page area. 
   MYDB.SCHEMA(ADMIN)=> SHOW PROCEDURE returntwo VERBOSE;
 RESULT                  | PROCEDURE | BUILTIN |  ARGUMENTS  | OWNER | EXECUTEDASOWNER |
VARARGS | DESCRIPTION | PROCEDURESOURCE
-------------------------+-----------+---------+-------------+-------+-----------------+
--------+--------------+-----------------
REFTABLE(MYDB.USER.TBL) | RETURNTWO | f      | (TIMESTAMP) | USER  |    t           |
f       |This is a sample stored procedure |
  DECLARE
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)';
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)';
    RETURN REFTABLE;
  END;

(1 row)
If the stored procedure is obfuscated, the VERBOSE mode shows the obfuscated body text, as follows: 
   MYDB.SCHEMA(ADMIN)=> SHOW PROCEDURE customer VERBOSE;
 RESULT | PROCEDURE   | BUILTIN |  ARGUMENTS  | OWNER | EXECUTEDASOWNER | VARARGS |
DESCRIPTION | PROCEDURESOURCE
-------------------------+-----------+---------+-------------+-------+-----------------+
--------+--------------+-----------------
BIGINT  | CUSTOMER    | f      |   ()        | USR  |    t            | f       |
            | TlpQU1FMV1JBUDEwVE5jZlh5TnpYbndCNkV5VFFMRTBiQT09JGE5N2p6ZEdJSVZwTTRrWmRRM0I3
WmUxZERZeWd6YkdjTWkxTzQrL1dCMmpqRGQvak9lUzFQQjArNGdlM08yZVdxUjRIMTFaTnROUmwKdk5xSm0wb1RPZz
09
(1 row)
To list all the stored procedures in a database, enter:
   MYDB.SCHEMA(ADMIN)=> SHOW PROCEDURE ALL;
           RESULT           |    PROCEDURE     | BUILTIN | ARGUMENTS
----------------------------+------------------+---------+------------------------
 BOOLEAN                    | BOOLPROC         | f       | (BOOLEAN)
 CHARACTER                  | CHARPROC         | f       | (CHARACTER(ANY))
 CHARACTER                  | CHARPROCANY      | f       | (CHARACTER(ANY))
 CHARACTER                  | CHARPROCANY2     | f       | (CHARACTER(10))
 REAL                       | FLOAT4PROC       | f       | (REAL)
 DOUBLE PRECISION           | FLOAT8PROC       | f       | (DOUBLE PRECISION)
 BYTEINT                    | INT1PROC         | f       | (BYTEINT)