system.tables

​

Description

​

Columns

• database (String) — The name of the database the table is in.
• name (String) — Table name.
• uuid (UUID) — Table uuid (Atomic database).
• engine (String) — Table engine name (without parameters).
• is_temporary (UInt8) — Flag that indicates whether the table is temporary.
• data_paths (Array(String)) — Paths to the table data in the file systems.
• metadata_path (String) — Path to the table metadata in the file system.
• metadata_modification_time (DateTime) — Time of latest modification of the table metadata.
• metadata_version (Int32) — Metadata version for ReplicatedMergeTree table, 0 for non ReplicatedMergeTree table.
• dependencies_database (Array(String)) — Database dependencies.
• dependencies_table (Array(String)) — Table dependencies (materialized views the current table).
• create_table_query (String) — The query that was used to create the table.
• engine_full (String) — Parameters of the table engine.
• as_select (String) — SELECT query for view.
• parameterized_view_parameters (Array(Tuple(name String, type String))) — Parameters of parameterized view.
• partition_key (String) — The partition key expression specified in the table.
• sorting_key (String) — The sorting key expression specified in the table.
• primary_key (String) — The primary key expression specified in the table.
• sampling_key (String) — The sampling key expression specified in the table.
• unique_key (String) — The unique key expression specified in the table (UNIQUE KEY clause).
• skipping_indices_types (Array(String)) — An array of the distinct types of data skipping indices defined on the table (for example minmax, set, bloom_filter, ngrambf_v1, tokenbf_v1, text, vector_similarity). Empty for tables without skip indices.
• storage_policy (String) — The storage policy. Relevant for tables using MergeTree and Distributed engines.
• total_rows (Nullable(UInt64)) — Total number of rows, if it is possible to quickly determine exact number of rows in the table, otherwise NULL (including underlying Buffer table).
• total_bytes (Nullable(UInt64)) — Total number of bytes, if it is possible to quickly determine exact number of bytes for the table on storage, otherwise NULL (does not includes any underlying storage). If the table stores data on disk, returns used space on disk (i.e. compressed). If the table stores data in memory, returns approximated number of used bytes in memory.
• total_bytes_uncompressed (Nullable(UInt64)) — Total number of uncompressed bytes, if it’s possible to quickly determine the exact number of bytes from the part checksums for the table on storage, otherwise NULL (does not take underlying storage (if any) into account).
• parts (Nullable(UInt64)) — The total number of parts in this table.
• active_parts (Nullable(UInt64)) — The number of active parts in this table.
• total_marks (Nullable(UInt64)) — The total number of marks in all parts in this table.
• active_on_fly_data_mutations (UInt64) — Total number of active data mutations (UPDATEs and DELETEs) suitable for applying on the fly.
• active_on_fly_alter_mutations (UInt64) — Total number of active alter mutations (MODIFY COLUMN) suitable for applying on the fly.
• active_on_fly_metadata_mutations (UInt64) — Total number of active metadata mutations (RENAMEs) suitable for applying on the fly.
• columns_descriptions_cache_size (UInt64) — Size of columns description cache for *MergeTree tables
• lifetime_rows (Nullable(UInt64)) — Total number of rows INSERTed since server start (only for Buffer tables).
• lifetime_bytes (Nullable(UInt64)) — Total number of bytes INSERTed since server start (only for Buffer tables).
• comment (String) — The comment for the table.
• has_own_data (UInt8) — Flag that indicates whether the table itself stores some data on disk or only accesses some other source.
• loading_dependencies_database (Array(String)) — Database loading dependencies (list of objects which should be loaded before the current object).
• loading_dependencies_table (Array(String)) — Table loading dependencies (list of objects which should be loaded before the current object).
• loading_dependent_database (Array(String)) — Dependent loading database.
• loading_dependent_table (Array(String)) — Dependent loading table.
• target_database (String) — For a materialized view, the database of the destination table the view writes to (the TO target, or the implicit .inner.* table). Empty for other engines.
• target_table (String) — For a materialized view, the name of the destination table the view writes to (the TO target, or the implicit .inner.* table). Empty for other engines.
• definer (String) — SQL security definer’s name used for the table.
• modification_hash (Nullable(UInt128)) — A value that changes whenever the data behind the table changes (similar to an HTTP ETag). For engines that can provide a loop-free signal (e.g. MergeTree, Memory, Log) it never returns to an earlier value across a change-and-change-back; for URL and object storage it is the resource’s strong ETag, which is best-effort, as it can repeat if the content is rewritten back to an identical state. Merge and Distributed combine their underlying tables’ values and are likewise best-effort if the set of underlying tables changes during a query (e.g. a table matching a Merge pattern is created and dropped mid-query). A View reports the combined value of the tables behind its stored SELECT (plus its own definition), and a MaterializedView reports the value of its target table, so wrappers such as Merge over a view work as well; a SQL SECURITY DEFINER or NONE view reports NULL to avoid exposing changes in its source tables through system.tables. Other views report NULL if the user lacks SELECT access on the tables read behind the view, if the view’s SELECT calls a non-deterministic function, or if the view has no real UUID (an Ordinary database). Merge, Distributed and view tables also report NULL when an underlying table has an active row policy for the current user in this server’s context (the policy filters what the user reads without the underlying table changing; row policies that exist only on remote shard servers are not seen). For a table on a remote shard, Distributed only accepts engines whose value can be validated from a separate probe query (the MergeTree family, Memory, Log, TinyLog, StripeLog) and reports NULL otherwise (e.g. over remote URL, object-storage, Merge, Distributed or view tables). It is not a hash of the data, so two tables with identical data may have different values. It covers the table’s regular columns and data only, not query-visible virtual columns that expose placement or external metadata (e.g. _disk_name, _tags, _headers), so a change affecting only such a virtual column may not be detected. NULL if the engine cannot tell whether its data has changed. Computing it may be expensive for some engines (e.g. Merge, Distributed, URL), so it is only calculated when this column is selected.

• table — Alias for name.

​

Example

SELECT * FROM system.tables LIMIT 2 FORMAT Vertical;

Row 1:
──────
database:                   base
name:                       t1
uuid:                       81b1c20a-b7c6-4116-a2ce-7583fb6b6736
engine:                     MergeTree
is_temporary:               0
data_paths:                 ['/var/lib/clickhouse/store/81b/81b1c20a-b7c6-4116-a2ce-7583fb6b6736/']
metadata_path:              /var/lib/clickhouse/store/461/461cf698-fd0b-406d-8c01-5d8fd5748a91/t1.sql
metadata_modification_time: 2021-01-25 19:14:32
dependencies_database:      []
dependencies_table:         []
create_table_query:         CREATE TABLE base.t1 (`n` UInt64) ENGINE = MergeTree ORDER BY n
engine_full:                MergeTree ORDER BY n
as_select:                  SELECT database AS table_catalog
partition_key:
sorting_key:                n
primary_key:                n
sampling_key:
skipping_indices_types:     []
storage_policy:             default
total_rows:                 1
total_bytes:                99
lifetime_rows:              ᴺᵁᴸᴸ
lifetime_bytes:             ᴺᵁᴸᴸ
comment:
has_own_data:               0
loading_dependencies_database: []
loading_dependencies_table:    []
loading_dependent_database:    []
loading_dependent_table:       []

Row 2:
──────
database:                   default
name:                       53r93yleapyears
uuid:                       00000000-0000-0000-0000-000000000000
engine:                     MergeTree
is_temporary:               0
data_paths:                 ['/var/lib/clickhouse/data/default/53r93yleapyears/']
metadata_path:              /var/lib/clickhouse/metadata/default/53r93yleapyears.sql
metadata_modification_time: 2020-09-23 09:05:36
dependencies_database:      []
dependencies_table:         []
create_table_query:         CREATE TABLE default.`53r93yleapyears` (`id` Int8, `febdays` Int8) ENGINE = MergeTree ORDER BY id
engine_full:                MergeTree ORDER BY id
as_select:                  SELECT name AS catalog_name
partition_key:
sorting_key:                id
primary_key:                id
sampling_key:
skipping_indices_types:     []
storage_policy:             default
total_rows:                 2
total_bytes:                155
lifetime_rows:              ᴺᵁᴸᴸ
lifetime_bytes:             ᴺᵁᴸᴸ
comment:
has_own_data:               0
loading_dependencies_database: []
loading_dependencies_table:    []
loading_dependent_database:    []
loading_dependent_table:       []