SHOW Statements
SHOW CREATE (TABLE|DATABASE|USER) hides secrets unless the following settings are turned on:
display_secrets_in_show_and_select(server setting)format_display_secrets_in_show_and_select(format setting)
Additionally, the user should have the displaySecretsInShowAndSelect privilege.
SHOW CREATE TABLE | DICTIONARY | VIEW | DATABASE
These statements return a single column of type String,
containing the CREATE query used for creating the specified object.
Syntax
SHOW TABLE t and SHOW DATABASE db have the same meaning as SHOW CREATE TABLE|DATABASE t|db, but SHOW t and SHOW db are not supported.
if you use this statement to get the CREATE query of system tables,
you will get a fake query, which only declares the table structure,
but cannot be used to create a table.
SHOW DATABASES
This statement prints a list of all databases.
Syntax
It is identical to the query:
Examples
In this example we use SHOW to obtain database names containing the symbol sequence 'de' in their names:
We can also do so in a case-insensitive manner:
Or get database names which do not contain 'de' in their names:
Finally, we can get the names of only the first two databases:
See also
SHOW TABLES
The SHOW TABLES statement displays a list of tables.
Syntax
If the FROM clause is not specified, the query returns a list of tables from the current database.
This statement is identical to the query:
Examples
In this example we use the SHOW TABLES statement to find all tables containing 'user' in their names:
We can also do so in a case-insensitive manner:
Or to find tables which don't contain the letter 's' in their names:
Finally, we can get the names of only the first two tables:
See also
SHOW COLUMNS
The SHOW COLUMNS statement displays a list of columns.
Syntax
The database and table name can be specified in abbreviated form as <db>.<table>,
meaning that FROM tab FROM db and FROM db.tab are equivalent.
If no database is specified, the query returns the list of columns from the current database.
There are also two optional keywords: EXTENDED and FULL. The EXTENDED keyword currently has no effect,
and exists for MySQL compatibility. The FULL keyword causes the output to include the collation, comment and privilege columns.
The SHOW COLUMNS statement produces a result table with the following structure:
| Column | Description | Type |
|---|---|---|
field | The name of the column | String |
type | The column data type. If the query was made through the MySQL wire protocol, then the equivalent type name in MySQL is shown. | String |
null | YES if the column data type is Nullable, NO otherwise | String |
key | PRI if the column is part of the primary key, SOR if the column is part of the sorting key, empty otherwise | String |
default | Default expression of the column if it is of type ALIAS, DEFAULT, or MATERIALIZED, otherwise NULL. | Nullable(String) |
extra | Additional information, currently unused | String |
collation | (only if FULL keyword was specified) Collation of the column, always NULL because ClickHouse has no per-column collations | Nullable(String) |
comment | (only if FULL keyword was specified) Comment on the column | String |
privilege | (only if FULL keyword was specified) The privilege you have on this column, currently not available | String |
Examples
In this example we'll use the SHOW COLUMNS statement to get information about all columns in table 'orders',
starting from 'delivery_':
See also
SHOW DICTIONARIES
The SHOW DICTIONARIES statement displays a list of Dictionaries.
Syntax
If the FROM clause is not specified, the query returns the list of dictionaries from the current database.
You can get the same results as the SHOW DICTIONARIES query in the following way:
Examples
The following query selects the first two rows from the list of tables in the system database, whose names contain reg.
SHOW INDEX
Displays a list of primary and data skipping indexes of a table.
This statement mostly exists for compatibility with MySQL. System tables system.tables (for
primary keys) and system.data_skipping_indices (for data skipping indices)
provide equivalent information but in a fashion more native to ClickHouse.
Syntax
The database and table name can be specified in abbreviated form as <db>.<table>, i.e. FROM tab FROM db and FROM db.tab are
equivalent. If no database is specified, the query assumes the current database as database.
The optional keyword EXTENDED currently has no effect, and exists for MySQL compatibility.
The statement produces a result table with the following structure:
| Column | Description | Type |
|---|---|---|
table | The name of the table. | String |
non_unique | Always 1 as ClickHouse does not support uniqueness constraints. | UInt8 |
key_name | The name of the index, PRIMARY if the index is a primary key index. | String |
seq_in_index | For a primary key index, the position of the column starting from 1. For a data skipping index: always 1. | UInt8 |
column_name | For a primary key index, the name of the column. For a data skipping index: '' (empty string), see field "expression". | String |
collation | The sorting of the column in the index: A if ascending, D if descending, NULL if unsorted. | Nullable(String) |
cardinality | An estimation of the index cardinality (number of unique values in the index). Currently always 0. | UInt64 |
sub_part | Always NULL because ClickHouse does not support index prefixes like MySQL. | Nullable(String) |
packed | Always NULL because ClickHouse does not support packed indexes (like MySQL). | Nullable(String) |
null | Currently unused | |
index_type | The index type, e.g. PRIMARY, MINMAX, BLOOM_FILTER etc. | String |
comment | Additional information about the index, currently always '' (empty string). | String |
index_comment | '' (empty string) because indexes in ClickHouse cannot have a COMMENT field (like in MySQL). | String |
visible | If the index is visible to the optimizer, always YES. | String |
expression | For a data skipping index, the index expression. For a primary key index: '' (empty string). | String |
Examples
In this example we use the SHOW INDEX statement to get information about all indexes in table 'tbl'
See also
SHOW PROCESSLIST
Outputs the content of the system.processes table, that contains a list of queries that is being processed at the moment, excepting SHOW PROCESSLIST queries.
Syntax
The SELECT * FROM system.processes query returns data about all the current queries.
Execute in the console:
SHOW GRANTS
The SHOW GRANTS statement shows privileges for a user.
Syntax
If the user is not specified, the query returns privileges for the current user.
The WITH IMPLICIT modifier allows showing the implicit grants (e.g., GRANT SELECT ON system.one)
The FINAL modifier merges all grants from the user and its granted roles (with inheritance)
SHOW CREATE USER
The SHOW CREATE USER statement shows parameters which were used at user creation.
Syntax
SHOW CREATE ROLE
The SHOW CREATE ROLE statement shows parameters which were used at role creation.
Syntax
SHOW CREATE ROW POLICY
The SHOW CREATE ROW POLICY statement shows parameters which were used at row policy creation.
Syntax
SHOW CREATE QUOTA
The SHOW CREATE QUOTA statement shows parameters which were used at quota creation.
Syntax
SHOW CREATE SETTINGS PROFILE
The SHOW CREATE SETTINGS PROFILE statement shows parameters which were used at settings profile creation.
Syntax
SHOW USERS
The SHOW USERS statement returns a list of user account names.
To view user accounts parameters, see the system table system.users.
Syntax
SHOW ROLES
The SHOW ROLES statement returns a list of roles.
To view other parameters,
see system tables system.roles and system.role_grants.
Syntax
SHOW PROFILES
The SHOW PROFILES statement returns a list of setting profiles.
To view user accounts parameters, see system table settings_profiles.
Syntax
SHOW POLICIES
The SHOW POLICIES statement returns a list of row policies for the specified table.
To view user accounts parameters, see system table system.row_policies.
Syntax
SHOW QUOTAS
The SHOW QUOTAS statement returns a list of quotas.
To view quotas parameters, see the system table system.quotas.
Syntax
SHOW QUOTA
The SHOW QUOTA statement returns a quota consumption for all users or for current user.
To view other parameters, see system tables system.quotas_usage and system.quota_usage.
Syntax
SHOW ACCESS
The SHOW ACCESS statement shows all users, roles, profiles, etc. and all their grants.
Syntax
SHOW CLUSTER(S)
The SHOW CLUSTER(S) statement returns a list of clusters.
All available clusters are listed in the system.clusters table.
The SHOW CLUSTER name query displays the contents of system.clusters table for the specified cluster name.
Syntax
Examples
SHOW SETTINGS
The SHOW SETTINGS statement returns a list of system settings and their values.
It selects data from the system.settings table.
Syntax
Clauses
LIKE|ILIKE allow to specify a matching pattern for the setting name. It can contain globs such as % or _. LIKE clause is case-sensitive, ILIKE — case insensitive.
When the CHANGED clause is used, the query returns only settings changed from their default values.
Examples
Query with the LIKE clause:
Query with the ILIKE clause:
Query with the CHANGED clause:
SHOW SETTING
The SHOW SETTING statement outputs setting value for specified setting name.
Syntax
See also
system.settingstable
SHOW FILESYSTEM CACHES
Examples
See also
system.settingstable
SHOW ENGINES
The SHOW ENGINES statement outputs the content of the system.table_engines table,
that contains description of table engines supported by server and their feature support information.
Syntax
See also
- system.table_engines table
SHOW FUNCTIONS
The SHOW FUNCTIONS statement outputs the content of the system.functions table.
Syntax
If either LIKE or ILIKE clause is specified, the query returns a list of system functions whose names match the provided <pattern>.
See Also
system.functionstable
SHOW MERGES
The SHOW MERGES statement returns a list of merges.
All merges are listed in the system.merges table:
| Column | Description |
|---|---|
table | Table name. |
database | The name of the database the table is in. |
estimate_complete | The estimated time to complete (in seconds). |
elapsed | The time elapsed (in seconds) since the merge started. |
progress | The percentage of completed work (0-100 percent). |
is_mutation | 1 if this process is a part mutation. |
size_compressed | The total size of the compressed data of the merged parts. |
memory_usage | Memory consumption of the merge process. |