Categories:
UDF (User-Defined Function) & Stored Procedure DDL

SHOW PROCEDURES

Lists the stored procedures that you have privileges to access.

For more information about stored procedures, see Working with Stored Procedures.

See also:
ALTER PROCEDURE , CREATE PROCEDURE , DROP PROCEDURE

Syntax

SHOW PROCEDURES [ LIKE '<pattern>' ]
                [ IN { ACCOUNT | DATABASE [ <db_name> ] | [ SCHEMA ] [ <schema_name> ] } ]

Parameters

LIKE 'pattern'

Filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...
... LIKE '%TESTING%' ...
IN ACCOUNT | [ DATABASE ] db_name | [ SCHEMA ] schema_name

Specifies the scope of the command, which determines whether the command lists records only for the current/specified database or schema, or across your entire account:

The DATABASE or SCHEMA keyword is not required; you can set the scope by specifying only the database or schema name. Likewise, the database or schema name is not required if the session currently has a database in use:

  • If DATABASE or SCHEMA is specified without a name and the session does not currently have a database in use, the parameter has no effect on the output.
  • If SCHEMA is specified with a name and the session does not currently have a database in use, the schema name must be fully qualified with the database name (e.g. testdb.testschema).

Default: Depends on whether the session currently has a database in use:

DB in use:DATABASE is the default (i.e. the command returns the objects you have privileges to view in the database).
DB not in use:ACCOUNT is the default (i.e. the command returns the objects you have privileges to view in your account).

Usage Notes

  • The command does not require a running warehouse to execute.

  • The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.

    To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Information Schema.

  • To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

Examples

Show all procedures:

SHOW PROCEDURES;

This example shows how to use SHOW PROCEDURE on a stored procedure that has a parameter. This also shows how to limit the list of procedures to those that match the specified regular expression.

SHOW PROCEDURES LIKE 'area_of_%';
+-------------------------------+----------------+--------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------+------------------------+------------------+-------------------+----------------------+-----------+
| created_on                    | name           | schema_name        | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments | arguments                          | description            | catalog_name     | is_table_function | valid_for_clustering | is_secure |
|-------------------------------+----------------+--------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------+------------------------+------------------+-------------------+----------------------+-----------|
| 2019-04-16 20:07:16.942 -0700 | AREA_OF_CIRCLE | TEMPORARY_DOC_TEST | N          | N            | N       |                 1 |                 1 | AREA_OF_CIRCLE(FLOAT) RETURN FLOAT | user-defined procedure | MGILKEY_TEST_DB1 | N                 | N                    | N         |
+-------------------------------+----------------+--------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------+------------------------+------------------+-------------------+----------------------+-----------+

The output columns are similar to the output columns for SHOW FUNCTIONS and SHOW USER FUNCTIONS. For stored procedures, some of these columns are not currently meaningful (e.g. is_aggregate, is_table_function, valid_for_clustering), but are reserved for future use.