SHOW CREATE TABLE¶
SHOW CREATE [TEMPORARY] TABLE [db.]table [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 table.
SHOW DATABASES [INTO OUTFILE filename] [FORMAT format]
Prints a list of all databases. This query is identical to
SELECT name FROM system.databases [INTO OUTFILE filename] [FORMAT format].
See also the section "Formats".
SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]
Outputs a list of queries currently being processed, other than
SHOW PROCESSLIST queries.
Prints a table containing the columns:
user – The user who made the query. Keep in mind that for distributed processing, queries are sent to remote servers under the 'default' user. SHOW PROCESSLIST shows the username for a specific query, not for a query that this query initiated.
address – The name of the host that the query was sent from. For distributed processing, on remote servers, this is the name of the query requestor host. To track where a distributed query was originally made from, look at SHOW PROCESSLIST on the query requestor server.
elapsed – The execution time, in seconds. Queries are output in order of decreasing execution time.
rows_read, bytes_read – How many rows and bytes of uncompressed data were read when processing the query. For distributed processing, data is totaled from all the remote servers. This is the data used for restrictions and quotas.
memory_usage – Current RAM usage in bytes. See the setting 'max_memory_usage'.
query – The query itself. In INSERT queries, the data for insertion is not output.
query_id – The query identifier. Non-empty only if it was explicitly defined by the user. For distributed processing, the query ID is not passed to remote servers.
This query is nearly identical to:
SELECT * FROM system.processes. The difference is that the
SHOW PROCESSLIST query does not show itself in a list, when the
SELECT .. FROM system.processes query does.
Tip (execute in the console):
$ watch -n1 "clickhouse-client --query='SHOW PROCESSLIST'"
Displays a list of tables.
SHOW [TEMPORARY] TABLES [FROM <db>] [LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
FROM clause is not specified, the query returns the list of tables from the current database.
The same result as the
SHOW TABLES query returns, you can get by the following way:
SELECT name FROM system.tables WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
The following query selects the first two rows from the list of tables in the
system database, whose names contain
SHOW TABLES FROM system LIKE '%co%' LIMIT 2
┌─name───────────────────────────┐ │ aggregate_function_combinators │ │ collations │ └────────────────────────────────┘