Categories:

DDL for User-Defined Functions, External Functions, and Stored Procedures

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 , DESCRIBE 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:

  • Database: DATABASE is the default (i.e. the command returns the objects you have privileges to view in the database).

  • No database: 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 Snowflake 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.

Output

The command’s output provides column properties and metadata in the following columns:

| 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 |

Column

Description

created_on

The timestamp at which the stored procedure was created.

name

The name of the stored procedure.

schema_name

The name of the schema in which the stored procedure exists.

is_builtin

Y if the stored procedure is built-in (rather than user-defined); N otherwise.

is_aggregate

Not applicable currently.

is_ansi

Y if the stored procedure is defined in the ANSI standard; N otherwise.

min_num_arguments

The minimum number of arguments.

max_num_arguments

The maximum number of arguments.

arguments

The data types of the arguments and of the return types.

description

A description of the stored procedure.

catalog_name

The name of the database in which the stored procedure exists.

is_table_function

Not applicable currently.

valid_for_clustering

Not applicable currently.

is_secure

Y if the stored procedure is a secure stored procedure; N otherwise.

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 |
|-------------------------------+----------------+--------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------+------------------------+-----------------------+-------------------+----------------------+-----------|
| 1967-06-23 00:00:00.123 -0700 | AREA_OF_CIRCLE | TEMPORARY_DOC_TEST | N          | N            | N       |                 1 |                 1 | AREA_OF_CIRCLE(FLOAT) RETURN FLOAT | user-defined procedure | TEMPORARY_DOC_TEST_DB | 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.

⇧
Back to top