SHOW USER FUNCTIONS¶

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

For a command that lists all functions, including built-in functions, see SHOW FUNCTIONS.

See also:

SHOW FUNCTIONS, SHOW EXTERNAL FUNCTIONS, FUNCTIONS view (Information Schema), FUNCTIONS view (Account Usage)

Syntax¶

SHOW USER FUNCTIONS [ 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 provides user function properties and metadata in the following columns:

Column

Description

created_on

Timestamp at which the user-defined function (UDF) was created.

name

Name of the UDF.

schema_name

Name of the schema in which the UDF exists.

is_builtin

Always N for user-defined functions. See SHOW FUNCTIONS for a command to list all functions, including built-in functions.

is_aggregate

Y if the function is an aggregate function; N otherwise.

is_ansi

Not applicable currently.

min_num_arguments

Minimum number of arguments to the UDF.

max_num_arguments

Maximum number of arguments to the UDF.

arguments

Data types of the arguments and return value.

description

Description of the UDF.

catalog_name

Name of the database in which the UDF exists.

is_table_function

Y if the UDF is a table function; N otherwise.

valid_for_clustering

Y if the UDF can be used in a CLUSTER BY expression; N otherwise.

is_secure

Y if the UDF is a secure UDF; N otherwise.

secrets

Map of secret values specified by the function’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 function’s EXTERNAL_ACCESS_INTEGRATION parameter.

is_external_function

Y if the function is an external function; N otherwise. See SHOW EXTERNAL FUNCTIONS for a command to list external functions.

language

Programming language of the UDF (for example, PYTHON or SQL).

is_memoizable

Y if the function is memoizable; N otherwise.

is_data_metric

Y if the function is a data metric function; N otherwise.

Examples¶

Show all the UDFs that you have privileges to view in the current database:

SHOW USER FUNCTIONS LIKE 'ALLOWED_REGIONS%' 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_integration | is_external_function | language | is_memoizable | is_data_metric |
---------------------------------+--------------------------+-------------+------------+--------------+---------+-------------------+-------------------+-----------------------------------------+-----------------------+----------------+-------------------+----------------------+-----------+---------+-----------------------------+----------------------+----------+---------------+----------------+
 Fri, 23 Jun 1967 00:00:00 -0700 | ALLOWED_REGIONS          | PUBLIC      | N          | N            | N       | 0                 | 0                 | ALLOWED_REGIONS() RETURN ARRAY          | user-defined function | MEMO_FUNC_TEST | N                 | N                    | N         |         |                             | N                    | SQL      | Y             | N              |
 Fri, 23 Jun 1967 00:00:00 -0700 | ALLOWED_REGIONS_NON_MEMO | PUBLIC      | N          | N            | N       | 0                 | 0                 | ALLOWED_REGIONS_NON_MEMO() RETURN ARRAY | user-defined function | MEMO_FUNC_TEST | N                 | N                    | N         |         |                             | N                    | SQL      | N             | N              |
---------------------------------+--------------------------+-------------+------------+--------------+---------+-------------------+-------------------+-----------------------------------------+-----------------------+----------------+-------------------+----------------------+-----------+---------+-----------------------------+----------------------+----------+---------------+----------------+