SHOW USER PROCEDURES¶

Lists all user-defined procedures for which you have access privileges. Use this command to list the user-defined procedures for a specified database or schema (or the current database/schema for the session), application, or for your entire account.

For a command that lists all procedures, including both built-in and user-defind procedures, see SHOW PROCEDURES.

See also:: SHOW PROCEDURES, PROCEDURES view (Information Schema), PROCEDURES view (Account Usage), SHOW USER PROCEDURES

Syntax¶

SHOW USER PROCEDURES [ LIKE '<pattern>' ]
  [ IN
    {
      ACCOUNT                                         |

      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

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.

Output¶

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

Column	Description
`created_on`	Timestamp at which the procedure was created.
`name`	Name of the procedure.
`schema_name`	Name of the schema in which the procedure exists.
`is_builtin`	`Y` if the procedure is built in; `N` otherwise (always `N` for user-created procedures).
`is_aggregate`	Not applicable currently.
`is_ansi`	Not applicable currently.
`min_num_arguments`	Minimum number of arguments to the procedure.
`max_num_arguments`	Maximum number of arguments to the procedure.
`arguments`	Data types of the arguments and return value.
`description`	Description of the procedure.
`catalog_name`	Name of the database in which the procedure exists.
`is_table_function`	`Y` if the procedure returns a table; `N` otherwise.
`valid_for_clustering`	`Y` if the procedure can be used in a CLUSTER BY expression; `N` otherwise.
`is_secure`	`Y` if the procedure is a secure procedure; `N` otherwise.
`secrets`	Map of secret values specified by the procedure’s SECRETS parameter, where map keys are secret variable names and map values are secret object names.
`external_access_integrations`	Names of external access integrations specified by the procedure’s EXTERNAL_ACCESS_INTEGRATION parameter.

Examples¶

Show procedures that you have privileges to view in the current schema whose names begin with GET_:

SHOW USER PROCEDURES LIKE 'GET_%' IN SCHEMA;

Copy

-------------------------------+-----------------+-------------+------------+--------------+---------+-------------------+-------------------+---------------------------------------+------------------------+--------------+-------------------+----------------------+-----------+---------+------------------------------+
          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 | secrets | external_access_integrations |
-------------------------------+-----------------+-------------+------------+--------------+---------+-------------------+-------------------+---------------------------------------+------------------------+--------------+-------------------+----------------------+-----------+---------+------------------------------+
 2023-01-27 15:01:13.862 -0800 | GET_FILE        | PUBLIC      | N          | N            | N       | 1                 | 1                 | GET_FILE(VARCHAR) RETURN VARCHAR      | user-defined procedure | BOOKS_DB     | N                 | N                    | N         |         |                              |
 2023-03-23 10:38:10.423 -0700 | GET_NUM_RESULTS | PUBLIC      | N          | N            | N       | 1                 | 1                 | GET_NUM_RESULTS(VARCHAR) RETURN FLOAT | user-defined procedure | BOOKS_DB     | N                 | N                    | N         |         |                              |
 2023-03-23 09:47:55.840 -0700 | GET_RESULTS     | PUBLIC      | N          | N            | N       | 1                 | 1                 | GET_RESULTS(VARCHAR) RETURN TABLE ()  | user-defined procedure | BOOKS_DB     | Y                 | N                    | N         |         |                              |
-------------------------------+-----------------+-------------+------------+--------------+---------+-------------------+-------------------+---------------------------------------+------------------------+--------------+-------------------+----------------------+-----------+---------+------------------------------+