- Categories:
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, UDFs (User-Defined Functions), and External Functions.
- See also:
Syntax¶
SHOW FUNCTIONS [ 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 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:
Column |
Description |
---|---|
|
The timestamp at which the function was created. |
|
The function’s name. |
|
The name of the schema that the function exists in. (NULL for built-in functions.) |
|
True if the function is a built-in function; false otherwise. |
|
True if the function is an aggregate function; false otherwise. |
|
True if the function is defined as part of the ANSI SQL standard; false otherwise. |
|
Minimum number of arguments. |
|
Maximum number of arguments. |
|
Shows the data types of the arguments and of the return value. |
|
Description of the function. |
|
The name of the database that the function exists in. (NULL for built-in functions.) |
|
True if the function is a table function; false otherwise. |
|
True if the function can be used in a |
|
True if the function is a secure function; false otherwise. |
|
True if the function is an external function; false otherwise. |
Examples¶
Show all functions:
SHOW FUNCTIONS; ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+ created_on | name | schema_name | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments | arguments | description | ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+ | != | | Y | N | Y | 2 | 2 | !=(ANY, ANY) RETURN BOOLEAN, !=(ROW(ANY), ROW(ANY)) RETURN BOOLEAN | Checks if the first value is not equal to the second value | | % | | Y | N | N | 2 | 2 | %(NUMBER, NUMBER) RETURN NUMBER, %(FLOAT, FLOAT) RETURN FLOAT | Computes modulo of two numbers | ... ... ... | AND | | Y | N | Y | 2 | 2 | AND(BOOLEAN, BOOLEAN) RETURN BOOLEAN | returns TRUE if both inputs are TRUE, FALSE otherwise | ... ... ... | NOT | | Y | N | Y | 1 | 1 | NOT(BOOLEAN) RETURN BOOLEAN | returns TRUE if input is TRUE, FALSE otherwise | ... ... ... | OR | | Y | N | Y | 2 | 2 | OR(BOOLEAN, BOOLEAN) RETURN BOOLEAN | returns TRUE if either input is TRUE, FALSE otherwise | ... ... ... | 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. | ... ... ... | TYPEOF | | Y | N | N | 1 | 1 | TYPEOF(ANY) RETURN VARCHAR(268435456) | returns the SQL type of its input argument as a string | ... ... ... ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+
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 | ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+ | 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. | ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+