INFORMATION_SCHEMA

INFORMATION_SCHEMA (or: information_schema) is a system database which provides a (somewhat) standardized, DBMS-agnostic view on metadata of database objects. The views in INFORMATION_SCHEMA are generally inferior to normal system tables but tools can use them to obtain basic information in a cross-DBMS manner. The structure and content of views in INFORMATION_SCHEMA is supposed to evolve in a backwards-compatible way, i.e. only new functionality is added but existing functionality is not changed or removed. In terms of internal implementation, views in INFORMATION_SCHEMA usually map to normal system tables like system.columns, system.databases and system.tables.

SHOW TABLES FROM INFORMATION_SCHEMA;

-- or:
SHOW TABLES FROM information_schema;

┌─name────────────────────┐
│ CHARACTER_SETS          │
│ COLLATIONS              │
│ COLUMNS                 │
│ COLUMN_PRIVILEGES       │
│ ENGINES                 │
│ KEY_COLUMN_USAGE        │
│ REFERENTIAL_CONSTRAINTS │
│ SCHEMATA                │
│ SCHEMA_PRIVILEGES       │
│ STATISTICS              │
│ TABLES                  │
│ TABLE_PRIVILEGES        │
│ USER_PRIVILEGES         │
│ VIEWS                   │
│ character_sets          │
│ collations              │
│ column_privileges       │
│ columns                 │
│ engines                 │
│ key_column_usage        │
│ referential_constraints │
│ schema_privileges       │
│ schemata                │
│ statistics              │
│ table_privileges        │
│ tables                  │
│ user_privileges         │
│ views                   │
└─────────────────────────┘

INFORMATION_SCHEMA contains the following views:

CHARACTER_SETS
COLUMNS
COLUMN_PRIVILEGES
COLLATIONS
ENGINES
KEY_COLUMN_USAGE
REFERENTIAL_CONSTRAINTS
SCHEMATA
SCHEMA_PRIVILEGES
STATISTICS
TABLES
TABLE_PRIVILEGES
USER_PRIVILEGES
VIEWS

Case-insensitive equivalent views, e.g. INFORMATION_SCHEMA.columns are provided for reasons of compatibility with other databases. The same applies to all the columns in these views - both lowercase (for example, table_name) and uppercase (TABLE_NAME) variants are provided.

COLUMNS

Contains columns read from the system.columns system table and columns that are not supported in ClickHouse or do not make sense (always NULL), but must be by the standard. Columns:

table_catalog (String) — Currently unused.
table_schema (String) — Currently unused.
table_name (String) — Currently unused.
non_unique (Int32) — Currently unused.
index_schema (String) — Currently unused.
index_name (Nullable(String)) — Currently unused.
seq_in_index (UInt32) — Currently unused.
column_name (Nullable(String)) — Currently unused.
collation (Nullable(String)) — Currently unused.
cardinality (Nullable(Int64)) — Currently unused.
sub_part (Nullable(Int64)) — Currently unused.
packed (Nullable(String)) — Currently unused.
nullable (String) — Currently unused.
index_type (String) — Currently unused.
comment (String) — Currently unused.
index_comment (String) — Currently unused.
is_visible (String) — Currently unused.
expression (Nullable(String)) — Currently unused.

Example

Query

SELECT table_catalog,
       table_schema,
       table_name,
       column_name,
       ordinal_position,
       column_default,
       is_nullable,
       data_type,
       character_maximum_length,
       character_octet_length,
       numeric_precision,
       numeric_precision_radix,
       numeric_scale,
       datetime_precision,
       character_set_catalog,
       character_set_schema,
       character_set_name,
       collation_catalog,
       collation_schema,
       collation_name,
       domain_catalog,
       domain_schema,
       domain_name,
       column_comment,
       column_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (table_schema = currentDatabase() OR table_schema = '')
  AND table_name NOT LIKE '%inner%' 
LIMIT 1 
FORMAT Vertical;

Response

Row 1:
──────
table_catalog:            default
table_schema:             default
table_name:               describe_example
column_name:              id
ordinal_position:         1
column_default:
is_nullable:              0
data_type:                UInt64
character_maximum_length: ᴺᵁᴸᴸ
character_octet_length:   ᴺᵁᴸᴸ
numeric_precision:        64
numeric_precision_radix:  2
numeric_scale:            0
datetime_precision:       ᴺᵁᴸᴸ
character_set_catalog:    ᴺᵁᴸᴸ
character_set_schema:     ᴺᵁᴸᴸ
character_set_name:       ᴺᵁᴸᴸ
collation_catalog:        ᴺᵁᴸᴸ
collation_schema:         ᴺᵁᴸᴸ
collation_name:           ᴺᵁᴸᴸ
domain_catalog:           ᴺᵁᴸᴸ
domain_schema:            ᴺᵁᴸᴸ
domain_name:              ᴺᵁᴸᴸ

SCHEMATA

Contains columns read from the system.databases system table and columns that are not supported in ClickHouse or do not make sense (always NULL), but must be by the standard. Columns:

catalog_name (String) — The name of the database.
schema_name (String) — The name of the database.
schema_owner (String) — Schema owner name, always 'default'.
default_character_set_catalog (Nullable(String)) — NULL, not supported.
default_character_set_schema (Nullable(String)) — NULL, not supported.
default_character_set_name (Nullable(String)) — NULL, not supported.
sql_path (Nullable(String)) — NULL, not supported.

Example

Query

SELECT catalog_name,
       schema_name,
       schema_owner,
       default_character_set_catalog,
       default_character_set_schema,
       default_character_set_name,
       sql_path
FROM information_schema.schemata
WHERE schema_name ILIKE 'information_schema' 
LIMIT 1 
FORMAT Vertical;

Response

Row 1:
──────
catalog_name:                  INFORMATION_SCHEMA
schema_name:                   INFORMATION_SCHEMA
schema_owner:                  default
default_character_set_catalog: ᴺᵁᴸᴸ
default_character_set_schema:  ᴺᵁᴸᴸ
default_character_set_name:    ᴺᵁᴸᴸ
sql_path:                      ᴺᵁᴸᴸ

TABLES

Contains columns read from the system.tables system table. Columns:

table_catalog (String) — The name of the database in which the table is located.
table_schema (String) — The name of the database in which the table is located.
table_name (String) — Table name.
table_type (String) — Table type. Possible values:
- BASE TABLE
- VIEW
- FOREIGN TABLE
- LOCAL TEMPORARY
- SYSTEM VIEW
table_rows (Nullable(UInt64)) — The total number of rows. NULL if it could not be determined.
data_length (Nullable(UInt64)) — The size of the data on-disk. NULL if it could not be determined.
index_length (Nullable(UInt64)) — The total size of the primary key, secondary indexes, and all marks.
table_collation (Nullable(String)) — The table default collation. Always utf8mb4_0900_ai_ci.
table_comment (Nullable(String)) — The comment used when creating the table.

Example

Query

SELECT table_catalog, 
       table_schema, 
       table_name, 
       table_type, 
       table_collation, 
       table_comment
FROM INFORMATION_SCHEMA.TABLES
WHERE (table_schema = currentDatabase() OR table_schema = '')
  AND table_name NOT LIKE '%inner%'
LIMIT 1 
FORMAT Vertical;

Response

Row 1:
──────
table_catalog:   default
table_schema:    default
table_name:      describe_example
table_type:      BASE TABLE
table_collation: utf8mb4_0900_ai_ci
table_comment:   

VIEWS

Contains columns read from the system.tables system table, when the table engine View is used. Columns:

table_catalog (String) — The name of the database in which the table is located.
table_schema (String) — The name of the database in which the table is located.
table_name (String) — Table name.
view_definition (String) — SELECT query for view.
check_option (String) — NONE, no checking.
is_updatable (Enum8) — NO, the view is not updated.
is_insertable_into (Enum8) — Shows whether the created view is materialized. Possible values:
- NO — The created view is not materialized.
- YES — The created view is materialized.
is_trigger_updatable (Enum8) — NO, the trigger is not updated.
is_trigger_deletable (Enum8) — NO, the trigger is not deleted.
is_trigger_insertable_into (Enum8) — NO, no data is inserted into the trigger.

Example

Query

CREATE VIEW v (n Nullable(Int32), f Float64) AS SELECT n, f FROM t;
CREATE MATERIALIZED VIEW mv ENGINE = Null AS SELECT * FROM system.one;
SELECT table_catalog,
       table_schema,
       table_name,
       view_definition,
       check_option,
       is_updatable,
       is_insertable_into,
       is_trigger_updatable,
       is_trigger_deletable,
       is_trigger_insertable_into
FROM information_schema.views
WHERE table_schema = currentDatabase() 
LIMIT 1
FORMAT Vertical;

Response

Row 1:
──────
table_catalog:              default
table_schema:               default
table_name:                 mv
view_definition:            SELECT * FROM system.one
check_option:               NONE
is_updatable:               NO
is_insertable_into:         YES
is_trigger_updatable:       NO
is_trigger_deletable:       NO
is_trigger_insertable_into: NO

KEY_COLUMN_USAGE

Contains columns from the system.tables system table which are restricted by constraints. Columns:

constraint_catalog (String) — Currently unused. Always def.
constraint_schema (String) — The name of the schema (database) to which the constraint belongs.
constraint_name (Nullable(String)) — The name of the constraint.
table_catalog (String) — Currently unused. Always def.
table_schema (String) — The name of the schema (database) to which the table belongs.
table_name (String) — The name of the table that has the constraint.
column_name (Nullable(String)) — The name of the column that has the constraint.
ordinal_position (UInt32) — Currently unused. Always 1.
position_in_unique_constraint (Nullable(UInt32)) — Currently unused. Always NULL.
referenced_table_schema (Nullable(String)) — Currently unused. Always NULL.
referenced_table_name (Nullable(String)) — Currently unused. Always NULL.
referenced_column_name (Nullable(String)) — Currently unused. Always NULL.

Example

Query

CREATE TABLE test (i UInt32, s String) ENGINE MergeTree ORDER BY i;
SELECT constraint_catalog,
       constraint_schema,
       constraint_name,
       table_catalog,
       table_schema,
       table_name,
       column_name,
       ordinal_position,
       position_in_unique_constraint,
       referenced_table_schema,
       referenced_table_name,
       referenced_column_name
FROM information_schema.key_column_usage 
WHERE table_name = 'test' 
FORMAT Vertical;

Response

Row 1:
──────
constraint_catalog:            def
constraint_schema:             default
constraint_name:               PRIMARY
table_catalog:                 def
table_schema:                  default
table_name:                    test
column_name:                   i
ordinal_position:              1
position_in_unique_constraint: ᴺᵁᴸᴸ
referenced_table_schema:       ᴺᵁᴸᴸ
referenced_table_name:         ᴺᵁᴸᴸ
referenced_column_name:        ᴺᵁᴸᴸ

REFERENTIAL_CONSTRAINTS

Contains information about foreign keys. Currently returns an empty result (no rows) which is just enough to provide compatibility with 3rd party tools like Tableau Online. Columns:

constraint_catalog (String) — Currently unused.
constraint_schema (String) — Currently unused.
constraint_name (Nullable(String)) — Currently unused.
unique_constraint_catalog (String) — Currently unused.
unique_constraint_schema (String) — Currently unused.
unique_constraint_name (Nullable(String)) — Currently unused.
match_option (String) — Currently unused.
update_rule (String) — Currently unused.
delete_rule (String) — Currently unused.
table_name (String) — Currently unused.
referenced_table_name (String) — Currently unused.

STATISTICS

Provides information about table indexes. Currently returns an empty result (no rows) which is just enough to provide compatibility with 3rd party tools like Tableau Online.

COLLATIONS

Provides information about available collations. Contains the collations from system.collations plus two MySQL-compatibility rows: utf8mb4_0900_ai_ci — the collation that the MySQL protocol advertises in the handshake, in SCHEMATA.DEFAULT_COLLATION_NAME, and in TABLES.TABLE_COLLATION — and binary — the pseudo-collation that the MySQL protocol stamps on numeric and other non-string columns in result-set metadata — so that a MySQL-aware client following the advertised collations into this view finds them. Columns:

collation_name (String) — The name of the collation.
character_set_name (Nullable(String)) — The associated character set (utf8mb4 for utf8mb4_0900_ai_ci, binary for binary, otherwise NULL). Provided for MySQL compatibility.
id (Nullable(UInt64)) — The MySQL collation id (255 for utf8mb4_0900_ai_ci, 63 for binary, otherwise NULL). Provided for MySQL compatibility.
is_default (Nullable(String)) — Whether the collation is the default of its character set (Yes for both MySQL-compatibility rows, otherwise NULL). Provided for MySQL compatibility.
is_compiled (Nullable(String)) — Whether the collation is compiled into the server (Yes for both MySQL-compatibility rows, otherwise NULL). Provided for MySQL compatibility.
sortlen (Nullable(UInt64)) — The minimum sort length (0 for utf8mb4_0900_ai_ci, 1 for binary, otherwise NULL). Provided for MySQL compatibility.
pad_attribute (Nullable(String)) — The pad attribute, NO PAD or PAD SPACE (NO PAD for both MySQL-compatibility rows, otherwise NULL). Provided for MySQL compatibility.

CHARACTER_SETS

Provides a static list of character sets (utf8, utf8mb4, ascii, binary) for compatibility with MySQL tools. ClickHouse itself works with UTF-8. Columns:

character_set_name (String) — The name of the character set.

ENGINES

Provides information about available table engines, read from the system.table_engines system table. Provided for MySQL compatibility. Columns:

engine (String) — The name of the table engine.
support (String) — DEFAULT for the engine set as default_table_engine, otherwise YES.

USER_PRIVILEGES

Provides information about global privileges, read from the system.grants system table (rows with no database). The *_PRIVILEGES views show only positive, non-wildcard grants: partial revokes, wildcard grants (e.g. GRANT SELECT ON db_prefix*.*) and grants on specific named objects of parameterized privilege types (e.g. GRANT TABLE ENGINE ON TinyLog) cannot be represented in the MySQL-compatible format and are omitted, so a grantee restricted by a partial revoke is shown with the unrestricted grant. ON * grants of parameterized types are shown as plain global privileges. system.grants is the authoritative source. Unlike MySQL, the views show the grants of all users and roles, and reading them requires access to system.grants; without it the query fails with an ACCESS_DENIED exception. There is no GRANTOR column because ClickHouse does not record who issued a grant. Columns:

grantee (String) — The user or role the privilege is granted to, in MySQL format: 'name'@'%'. Roles have no MySQL analog and are rendered the same way as users.
table_catalog (String) — Always def.
privilege_type (String) — The name of the privilege, e.g. SELECT or ALTER UPDATE. See system.privileges for the full list.
is_grantable (String) — YES if the privilege is granted WITH GRANT OPTION, otherwise NO.
grantee_type (String) — USER or ROLE. Non-standard column: a user and a role may share the same name in ClickHouse, and this column tells such grantees apart.

SCHEMA_PRIVILEGES

Provides information about database-level privileges, read from the system.grants system table (rows with a database but no table). See USER_PRIVILEGES for the limitations shared by all *_PRIVILEGES views. Columns:

grantee (String) — The user or role the privilege is granted to, in MySQL format: 'name'@'%'.
table_catalog (String) — Always def.
table_schema (String) — The name of the database.
privilege_type (String) — The name of the privilege.
is_grantable (String) — YES if the privilege is granted WITH GRANT OPTION, otherwise NO.
grantee_type (String) — USER or ROLE. Non-standard column: a user and a role may share the same name in ClickHouse, and this column tells such grantees apart.

TABLE_PRIVILEGES

Provides information about table-level privileges, read from the system.grants system table (rows with a table but no column). See USER_PRIVILEGES for the limitations shared by all *_PRIVILEGES views. Columns:

grantee (String) — The user or role the privilege is granted to, in MySQL format: 'name'@'%'.
table_catalog (String) — Always def.
table_schema (String) — The name of the database.
table_name (String) — The name of the table.
privilege_type (String) — The name of the privilege.
is_grantable (String) — YES if the privilege is granted WITH GRANT OPTION, otherwise NO.
grantee_type (String) — USER or ROLE. Non-standard column: a user and a role may share the same name in ClickHouse, and this column tells such grantees apart.

COLUMN_PRIVILEGES

Provides information about column-level privileges, read from the system.grants system table (rows with a column; one row per column). See USER_PRIVILEGES for the limitations shared by all *_PRIVILEGES views. Columns:

grantee (String) — The user or role the privilege is granted to, in MySQL format: 'name'@'%'.
table_catalog (String) — Always def.
table_schema (String) — The name of the database.
table_name (String) — The name of the table.
column_name (String) — The name of the column.
privilege_type (String) — The name of the privilege.
is_grantable (String) — YES if the privilege is granted WITH GRANT OPTION, otherwise NO.
grantee_type (String) — USER or ROLE. Non-standard column: a user and a role may share the same name in ClickHouse, and this column tells such grantees apart.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

COLUMNS

SCHEMATA

TABLES

VIEWS

KEY_COLUMN_USAGE

REFERENTIAL_CONSTRAINTS

STATISTICS

COLLATIONS

CHARACTER_SETS

ENGINES

USER_PRIVILEGES

SCHEMA_PRIVILEGES

TABLE_PRIVILEGES

COLUMN_PRIVILEGES

​COLUMNS

​SCHEMATA

​TABLES

​VIEWS

​KEY_COLUMN_USAGE

​REFERENTIAL_CONSTRAINTS

​STATISTICS

​COLLATIONS

​CHARACTER_SETS

​ENGINES

​USER_PRIVILEGES

​SCHEMA_PRIVILEGES

​TABLE_PRIVILEGES

​COLUMN_PRIVILEGES

COLUMNS

SCHEMATA

TABLES

VIEWS

KEY_COLUMN_USAGE

REFERENTIAL_CONSTRAINTS

STATISTICS

COLLATIONS

CHARACTER_SETS

ENGINES

USER_PRIVILEGES

SCHEMA_PRIVILEGES

TABLE_PRIVILEGES

COLUMN_PRIVILEGES