SHOW PARAMETERS¶
Lists all the account, session, and object parameters that can be set, as well as the current and default values for each parameter:
Account parameters can only be set at the account level.
Session parameters can be set at the account, user, and session level.
Object parameters can be set at the account and object level.
If a parameter has been explicitly set, the output of this command also shows the level at which the parameter has been set.
For descriptions of the different parameter types, as well as detailed descriptions for each parameter, see Parameters.
Syntax¶
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 | FORIN ...orFOR ...specifies the scope of the command, which determines the parameters that are returned:SESSIONReturns all the session parameters and their settings for the current session. A user can change these parameters for their session using ALTER SESSION.
ACCOUNTReturns a list of the account, session, and object parameters that can be set at the account level. A user with the ACCOUNTADMIN role (i.e. account administrator) can change these parameters via ALTER ACCOUNT. For more information, see Parameter management.
USER [ name ]Returns a list of the session parameter defaults that are set for the specified user (or the current user) each time the user logs in.
If no user is specified, the command returns results for the current user.
An administrator with the appropriate user privileges can change the session parameter defaults for a user using ALTER USER.
Individual users can also change their session parameter defaults using ALTER USER.
WAREHOUSE | DATABASE | SCHEMA | TASK [ name ]Returns the object parameters that can be set for the current/specified object. Users with the appropriate privileges can change these parameters using the corresponding ALTER <object> command.
TABLE [ table_or_view_name ]Returns the object parameters that can be set for the specified table or view. Users with the appropriate privileges can change these parameters using the ALTER TABLE command.
Use
TABLEas the domain for all table-like objects, such as tables, views, and materialized views.
Default:
SESSION
Usage notes¶
The command doesn’t require a running warehouse to execute.
The command only returns objects for which the current user’s current role has been granted at least one access privilege.
The MANAGE GRANTS access privilege implicitly allows its holder to see every object in the account. By default, only the account administrator (users with the ACCOUNTADMIN role) and security administrator (users with the SECURITYADMIN role) have the MANAGE GRANTS privilege.
To post-process the output of this command, you can use the pipe operator (
->>) or the RESULT_SCAN function. Both constructs treat the output as a result set that you can query.For example, you can use the pipe operator or RESULT_SCAN function to select specific columns from the SHOW command output or filter the rows.
When you refer to the output columns, use double-quoted identifiers for the column names. For example, to select the output column
type, specifySELECT "type".You must use double-quoted identifiers because the output column names for SHOW commands are in lowercase. The double quotes ensure that the column names in the SELECT list or WHERE clause match the column names in the SHOW command output that was scanned.
Examples¶
Show all the session parameters that can be set for the current session:
Note that the output for this example does not include any of the account or object parameters because they cannot be set at the session level.
For more information about account parameters, as well as setting parameters at the account level, see Parameter management.
Show all the object parameters that can be set for the specified warehouse (testwh):
Show all the object parameters that can be set for the current database (testdb):