# SHOW FUNCTIONS¶

Lists all the native (i.e. system-defined/built-in) scalar functions provided by Snowflake, as well as any user-defined functions (UDFs) or external functions that have been created for your account.

For more information, see SQL Function Reference, Writing User-Defined Functions (UDFs), and Writing External Functions.

## Syntax¶

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

DATABASE                 |
DATABASE <database_name> |

SCHEMA                   |
SCHEMA <schema_name>     |
<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 ... ]

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 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 (e.g. db.schema).

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

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.

• The output of this command might include objects with names like SN_TEMP_OBJECT_<n> (where <n> is a number). These are temporary objects that are created by the Snowpark library on behalf of the user.

• The IS_MEMOIZABLE column is not included in the output when the 2022_08 behavior change release bundle is disabled.

For information on disabling behavior change release bundles, see Behavior Change Management.

## 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 | is_external_function | language | is_memoizable


Column

Description

created_on

The timestamp at which the function was created.

name

The function’s name.

schema_name

The name of the schema that the function exists in. (NULL for built-in functions.)

is_builtin

True if the function is a built-in function; false otherwise.

is_aggregate

True if the function is an aggregate function; false otherwise.

is_ansi

True if the function is defined as part of the ANSI SQL standard; false otherwise.

min_num_arguments

Minimum number of arguments.

max_num_arguments

Maximum number of arguments.

arguments

Shows the data types of the arguments and of the return value.

description

Description of the function.

catalog_name

The name of the database that the function exists in. (NULL for built-in functions.)

is_table_function

True if the function is a table function; false otherwise.

valid_for_clustering

True if the function can be used in a CLUSTER BY expression; false otherwise.

is_secure

True if the function is a secure function; false otherwise.

is_external_function

True if the function is an external function; false otherwise.

language

For built-in functions, this column shows “SQL”. . For user-defined functions, this column shows the language in which the function was written (e.g. “JAVASCRIPT” or “SQL”). . For external functions, this column shows “EXTERNAL”.

is_memoizable

Y (yes) if the function is memoizable, N (no) otherwise.

## Usage Notes¶

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

• The IS_MEMOIZABLE column is not included in the output when the 2022_08 behavior change release bundle is disabled.

For information on disabling behavior change release bundles, see Behavior Change Management.

## Examples¶

Show all functions:

SHOW FUNCTIONS;


Show only functions matching the specified regular expression:

SHOW FUNCTIONS LIKE 'SQUARE';

------------+--------+-------------+------------+--------------+---------+-------------------+-------------------+----------------------------------------------------------------------+------------------------------------------------------------+----------+---------------+
created_on | name   | schema_name | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments |                               arguments                              |                      description                           | language | is_memoizable |
------------+--------+-------------+------------+--------------+---------+-------------------+-------------------+----------------------------------------------------------------------+------------------------------------------------------------+----------+---------------+
| SQUARE |             | Y          | N            | Y       | 1                 | 1                 | SQUARE(NUMBER(38,0)) RETURN NUMBER(38,0), SQUARE(FLOAT) RETURN FLOAT | Compute the square of the input expression.                | SQL      | N             |
------------+--------+-------------+------------+--------------+---------+-------------------+-------------------+----------------------------------------------------------------------+------------------------------------------------------------+----------+---------------+