- 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:
SHOW USER FUNCTIONS , SHOW EXTERNAL FUNCTIONS , CREATE FUNCTION , DROP FUNCTION , ALTER FUNCTION , DESCRIBE FUNCTION
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_nameSpecifies 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
DATABASEorSCHEMAkeyword 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
DATABASEorSCHEMAis specified without a name and the session does not currently have a database in use, the parameter has no effect on the output.If
SCHEMAis 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:
DATABASEis the default (i.e. the command returns the objects you have privileges to view in the database).No database:
ACCOUNTis 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.
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 |
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. |
|
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”. |
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 | language | ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+ | != | | 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 | SQL | | % | | Y | N | N | 2 | 2 | %(NUMBER, NUMBER) RETURN NUMBER, %(FLOAT, FLOAT) RETURN FLOAT | Computes modulo of two numbers | SQL | ... ... ... | AND | | Y | N | Y | 2 | 2 | AND(BOOLEAN, BOOLEAN) RETURN BOOLEAN | returns TRUE if both inputs are TRUE, FALSE otherwise | SQL | ... ... ... | NOT | | Y | N | Y | 1 | 1 | NOT(BOOLEAN) RETURN BOOLEAN | returns TRUE if input is TRUE, FALSE otherwise | SQL | ... ... ... | OR | | Y | N | Y | 2 | 2 | OR(BOOLEAN, BOOLEAN) RETURN BOOLEAN | returns TRUE if either input is TRUE, FALSE otherwise | SQL | ... ... ... | 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 | ... ... ... | TYPEOF | | Y | N | N | 1 | 1 | TYPEOF(ANY) RETURN VARCHAR(268435456) | returns the SQL type of its input argument as a string | SQL | ... ... ... ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+
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 | ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+ | 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 | ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+