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
orSCHEMA
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
orSCHEMA
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 |
---|---|
|
The timestamp at which the stored procedure was created. |
|
The name of the stored procedure. |
|
The name of the schema in which the stored procedure exists. |
|
|
|
Not applicable currently. |
|
|
|
The minimum number of arguments. |
|
The maximum number of arguments. |
|
The data types of the arguments and of the return types. |
|
A description of the stored procedure. |
|
The name of the database in which the stored procedure exists. |
|
Not applicable currently. |
|
Not applicable currently. |
|
|
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.