- 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 |
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:
ACCOUNTReturns records for the entire account.
DATABASE, .DATABASE db_nameReturns records for the current database in use or a specified database (
db_name).If you specify
DATABASEwithoutdb_nameand no database is in use, the keyword has no effect on the output.SCHEMA, .SCHEMA schema_name, .schema_nameReturns records for the current schema in use or a specified schema (
schema_name).SCHEMAis optional if a database is in use or if you specify the fully-qualifiedschema_name(e.g.db.schema).If no database is in use, specifying
SCHEMAhas no effect on the output.
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 | ------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+