Skip to main content

SHOW Statements

SHOW CREATE TABLE​

SHOW CREATE [TEMPORARY] [TABLE|DICTIONARY|VIEW] [db.]table|view [INTO OUTFILE filename] [FORMAT format]

Returns a single String-type β€˜statement’ column, which contains a single value – the CREATE query used for creating the specified object.

Note that if you use this statement to get CREATE query of system tables, you will get a fake query, which only declares table structure, but cannot be used to create table.

SHOW DATABASES​

Prints a list of all databases.

SHOW DATABASES [LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE filename] [FORMAT format]

This statement is identical to the query:

SELECT name FROM system.databases [WHERE name LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE filename] [FORMAT format]

Examples​

Getting database names, containing the symbols sequence 'de' in their names:

SHOW DATABASES LIKE '%de%'

Result:

β”Œβ”€name────┐
β”‚ default β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Getting database names, containing symbols sequence 'de' in their names, in the case insensitive manner:

SHOW DATABASES ILIKE '%DE%'

Result:

β”Œβ”€name────┐
β”‚ default β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Getting database names, not containing the symbols sequence 'de' in their names:

SHOW DATABASES NOT LIKE '%de%'

Result:

β”Œβ”€name───────────────────────────┐
β”‚ _temporary_and_external_tables β”‚
β”‚ system β”‚
β”‚ test β”‚
β”‚ tutorial β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Getting the first two rows from database names:

SHOW DATABASES LIMIT 2

Result:

β”Œβ”€name───────────────────────────┐
β”‚ _temporary_and_external_tables β”‚
β”‚ default β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

See Also​

SHOW PROCESSLIST​

SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]

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.

The SELECT * FROM system.processes query returns data about all the current queries.

Tip (execute in the console):

$ watch -n1 "clickhouse-client --query='SHOW PROCESSLIST'"

SHOW TABLES​

Displays a list of tables.

SHOW [TEMPORARY] TABLES [{FROM | IN} <db>] [LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

If the FROM clause is not specified, the query returns the list of tables from the current database.

This statement is identical to the query:

SELECT name FROM system.tables [WHERE name LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

Examples​

Getting table names, containing the symbols sequence 'user' in their names:

SHOW TABLES FROM system LIKE '%user%'

Result:

β”Œβ”€name─────────────┐
β”‚ user_directories β”‚
β”‚ users β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Getting table names, containing sequence 'user' in their names, in the case insensitive manner:

SHOW TABLES FROM system ILIKE '%USER%'

Result:

β”Œβ”€name─────────────┐
β”‚ user_directories β”‚
β”‚ users β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Getting table names, not containing the symbol sequence 's' in their names:

SHOW TABLES FROM system NOT LIKE '%s%'

Result:

β”Œβ”€name─────────┐
β”‚ metric_log β”‚
β”‚ metric_log_0 β”‚
β”‚ metric_log_1 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Getting the first two rows from table names:

SHOW TABLES FROM system LIMIT 2

Result:

β”Œβ”€name───────────────────────────┐
β”‚ aggregate_function_combinators β”‚
β”‚ asynchronous_metric_log β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

See Also​

SHOW DICTIONARIES​

Displays a list of external dictionaries.

SHOW DICTIONARIES [FROM <db>] [LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

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:

SELECT name FROM system.dictionaries WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

Example

The following query selects the first two rows from the list of tables in the system database, whose names contain reg.

SHOW DICTIONARIES FROM db LIKE '%reg%' LIMIT 2
β”Œβ”€name─────────┐
β”‚ regions β”‚
β”‚ region_names β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

SHOW GRANTS​

Shows privileges for a user.

Syntax​

SHOW GRANTS [FOR user1 [, user2 ...]]

If user is not specified, the query returns privileges for the current user.

SHOW CREATE USER​

Shows parameters that were used at a user creation.

SHOW CREATE USER does not output user passwords.

Syntax​

SHOW CREATE USER [name1 [, name2 ...] | CURRENT_USER]

SHOW CREATE ROLE​

Shows parameters that were used at a role creation.

Syntax​

SHOW CREATE ROLE name1 [, name2 ...]

SHOW CREATE ROW POLICY​

Shows parameters that were used at a row policy creation.

Syntax​

SHOW CREATE [ROW] POLICY name ON [database1.]table1 [, [database2.]table2 ...]

SHOW CREATE QUOTA​

Shows parameters that were used at a quota creation.

Syntax​

SHOW CREATE QUOTA [name1 [, name2 ...] | CURRENT]

SHOW CREATE SETTINGS PROFILE​

Shows parameters that were used at a settings profile creation.

Syntax​

SHOW CREATE [SETTINGS] PROFILE name1 [, name2 ...]

SHOW USERS​

Returns a list of user account names. To view user accounts parameters, see the system table system.users.

Syntax​

SHOW USERS

SHOW ROLES​

Returns a list of roles. To view another parameters, see system tables system.roles and system.role-grants.

Syntax​

SHOW [CURRENT|ENABLED] ROLES

SHOW PROFILES​

Returns a list of setting profiles. To view user accounts parameters, see the system table settings_profiles.

Syntax​

SHOW [SETTINGS] PROFILES

SHOW POLICIES​

Returns a list of row policies for the specified table. To view user accounts parameters, see the system table system.row_policies.

Syntax​

SHOW [ROW] POLICIES [ON [db.]table]

SHOW QUOTAS​

Returns a list of quotas. To view quotas parameters, see the system table system.quotas.

Syntax​

SHOW QUOTAS

SHOW QUOTA​

Returns a quota consumption for all users or for current user. To view another parameters, see system tables system.quotas_usage and system.quota_usage.

Syntax​

SHOW [CURRENT] QUOTA

SHOW ACCESS​

Shows all users, roles, profiles, etc. and all their grants.

Syntax​

SHOW ACCESS

SHOW CLUSTER(s)​

Returns a list of clusters. All available clusters are listed in the system.clusters table.

note

SHOW CLUSTER name query displays the contents of system.clusters table for this cluster.

Syntax​

SHOW CLUSTER '<name>'
SHOW CLUSTERS [LIKE|NOT LIKE '<pattern>'] [LIMIT <N>]

Examples​

Query:

SHOW CLUSTERS;

Result:

β”Œβ”€cluster──────────────────────────────────────┐
β”‚ test_cluster_two_shards β”‚
β”‚ test_cluster_two_shards_internal_replication β”‚
β”‚ test_cluster_two_shards_localhost β”‚
β”‚ test_shard_localhost β”‚
β”‚ test_shard_localhost_secure β”‚
β”‚ test_unavailable_shard β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Query:

SHOW CLUSTERS LIKE 'test%' LIMIT 1;

Result:

β”Œβ”€cluster─────────────────┐
β”‚ test_cluster_two_shards β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Query:

SHOW CLUSTER 'test_shard_localhost' FORMAT Vertical;

Result:

Row 1:
──────
cluster: test_shard_localhost
shard_num: 1
shard_weight: 1
replica_num: 1
host_name: localhost
host_address: 127.0.0.1
port: 9000
is_local: 1
user: default
default_database:
errors_count: 0
estimated_recovery_time: 0

SHOW SETTINGS​

Returns a list of system settings and their values. Selects data from the system.settings table.

Syntax

SHOW [CHANGED] SETTINGS LIKE|ILIKE <name>

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:

SHOW SETTINGS LIKE 'send_timeout';

Result:

β”Œβ”€name─────────┬─type────┬─value─┐
β”‚ send_timeout β”‚ Seconds β”‚ 300 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”˜

Query with the ILIKE clause:

SHOW SETTINGS ILIKE '%CONNECT_timeout%'

Result:

β”Œβ”€name────────────────────────────────────┬─type─────────┬─value─┐
β”‚ connect_timeout β”‚ Seconds β”‚ 10 β”‚
β”‚ connect_timeout_with_failover_ms β”‚ Milliseconds β”‚ 50 β”‚
β”‚ connect_timeout_with_failover_secure_ms β”‚ Milliseconds β”‚ 100 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”˜

Query with the CHANGED clause:

SHOW CHANGED SETTINGS ILIKE '%MEMORY%'

Result:

β”Œβ”€name─────────────┬─type───┬─value───────┐
β”‚ max_memory_usage β”‚ UInt64 β”‚ 10000000000 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

See Also

Original article