title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/information_schema.md)

`INFORMATION_SCHEMA` (or: `information_schema`) is a system database which provides a (somewhat) standardized, [DBMS-agnostic view](https://en.wikipedia.org/wiki/Information_schema) 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 evolves 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 to normal system tables like [system.columns](/docs/operations/system-tables/columns), [system.databases](/docs/operations/system-tables/databases) and [system.tables](/docs/operations/system-tables/tables).

```
SHOW TABLES FROM INFORMATION_SCHEMA;

-- or:
SHOW TABLES FROM information_schema;
```

```
┌─name────────────────────┐
│ COLUMNS                 │
│ KEY_COLUMN_USAGE        │
│ REFERENTIAL_CONSTRAINTS │
│ SCHEMATA                │
| STATISTICS              |
│ TABLES                  │
│ VIEWS                   │
│ columns                 │
│ key_column_usage        │
│ referential_constraints │
│ schemata                │
| statistics              |
│ tables                  │
│ views                   │
└─────────────────────────┘
```

`INFORMATION_SCHEMA` contains the following views:

- [COLUMNS](#columns)
- [KEY\_COLUMN\_USAGE](#key_column_usage)
- [REFERENTIAL\_CONSTRAINTS](#referential_constraints)
- [SCHEMATA](#schemata)
- [STATISTICS](#statistics)
- [TABLES](#tables)
- [VIEWS](#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[​](#columns "Direct link to COLUMNS")

Contains columns read from the [system.columns](/docs/operations/system-tables/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:

**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;
```

Result:

```
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[​](#schemata "Direct link to SCHEMATA")

Contains columns read from the [system.databases](/docs/operations/system-tables/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](/docs/sql-reference/data-types/string)) — The name of the database.
- `schema_name` ([String](/docs/sql-reference/data-types/string)) — The name of the database.
- `schema_owner` ([String](/docs/sql-reference/data-types/string)) — Schema owner name, always `'default'`.
- `default_character_set_catalog` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/string))) — `NULL`, not supported.
- `default_character_set_schema` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/string))) — `NULL`, not supported.
- `default_character_set_name` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/string))) — `NULL`, not supported.
- `sql_path` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/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;
```

Result:

```
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[​](#tables "Direct link to TABLES")

Contains columns read from the [system.tables](/docs/operations/system-tables/tables) system table.

Columns:

- `table_catalog` ([String](/docs/sql-reference/data-types/string)) — The name of the database in which the table is located.
- `table_schema` ([String](/docs/sql-reference/data-types/string)) — The name of the database in which the table is located.
- `table_name` ([String](/docs/sql-reference/data-types/string)) — Table name.
- `table_type` ([String](/docs/sql-reference/data-types/string)) — Table type. Possible values:
  - `BASE TABLE`
  - `VIEW`
  - `FOREIGN TABLE`
  - `LOCAL TEMPORARY`
  - `SYSTEM VIEW`
- `table_rows` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — The total number of rows. NULL if it could not be determined.
- `data_length` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — The size of the data on-disk. NULL if it could not be determined.
- `index_length` ([Nullable](/docs/sql-reference/data-types/nullable)([UInt64](/docs/sql-reference/data-types/int-uint))) — The total size of the primary key, secondary indexes, and all marks.
- `table_collation` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/string))) — The table default collation. Always `utf8mb4_0900_ai_ci`.
- `table_comment` ([Nullable](/docs/sql-reference/data-types/nullable)([String](/docs/sql-reference/data-types/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;
```

Result:

```
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[​](#views "Direct link to VIEWS")

Contains columns read from the [system.tables](/docs/operations/system-tables/tables) system table, when the table engine [View](/docs/engines/table-engines/special/view) is used.

Columns: