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                                         |

                       CLASS <class_name>                              |

                       DATABASE                                        |
                       DATABASE <database_name>                        |

                       SCHEMA                                          |
                       SCHEMA <schema_name>                            |
                       <schema_name>

                       APPLICATION <application_name>                  |
                       APPLICATION PACKAGE <application_package_name>  |
                     }
                ]
Copy

Parameters¶

LIKE 'pattern'

Optionally 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%' ...

. Default: No value (no filtering is applied to the output).

[ IN ... ]

Optionally specifies the scope of the command. Specify one of the following:

ACCOUNT

Returns records for the entire account.

DATABASE, . DATABASE db_name

Returns records for the current database in use or for a specified database (db_name).

If you specify DATABASE without db_name and no database is in use, the keyword has no effect on the output.

SCHEMA, . SCHEMA schema_name, . schema_name

Returns records for the current schema in use or a specified schema (schema_name).

SCHEMA is optional if a database is in use or if you specify the fully qualified schema_name (for example, db.schema).

If no database is in use, specifying SCHEMA has no effect on the output.

APPLICATION application_name, . APPLICATION PACKAGE application_package_name

Returns records for the named Snowflake Native App or application package.

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

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

  • No database: ACCOUNT is the default (that is, the command returns the objects you have privileges to view in your account).

Usage notes¶

  • Columns that start with the prefix is_ return either Y (yes) or N (no).

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

  • If you specify CLASS, the command only returns the following columns:

    | name | min_num_arguments | max_num_arguments | arguments | descriptions | language |
    

Output¶

The command output provides procedure properties and metadata in the following columns:

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. Optional arguments are displayed with the DEFAULT keyword.

description

A description of the stored procedure.

catalog_name

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

is_table_function

Y if the stored procedure returns tabular data; N otherwise.

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

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

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, valid_for_clustering), but are reserved for future use.