INFORMATION_SCHEMA

INFORMATION_SCHEMA (ou : information_schema) est une base de données système qui fournit une vue indépendante du SGBD, quoique seulement partiellement normalisée, des métadonnées des objets de base de données. Les vues de INFORMATION_SCHEMA sont généralement moins complètes que les tables système classiques, mais les outils peuvent les utiliser pour obtenir des informations de base de manière inter-SGBD. La structure et le contenu des vues de INFORMATION_SCHEMA sont conçus pour évoluer de manière rétrocompatible, c’est-à-dire que seules de nouvelles fonctionnalités sont ajoutées, sans modification ni suppression des fonctionnalités existantes. Du point de vue de l’implémentation interne, les vues de INFORMATION_SCHEMA correspondent généralement à des tables système classiques telles que system.columns, system.databases et system.tables.

SHOW TABLES FROM INFORMATION_SCHEMA;

-- or:
SHOW TABLES FROM information_schema;

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

INFORMATION_SCHEMA contient les vues suivantes :

• COLUMNS
• COLLATIONS
• KEY_COLUMN_USAGE
• REFERENTIAL_CONSTRAINTS
• SCHEMATA
• STATISTICS
• TABLES
• VIEWS

Des vues équivalentes non sensibles à la casse, par exemple INFORMATION_SCHEMA.columns, sont fournies pour des raisons de compatibilité avec d'autres bases de données. Il en va de même pour toutes les colonnes de ces vues : des variantes en minuscules (par exemple table_name) et en majuscules (TABLE_NAME) sont disponibles.

Contient les colonnes issues de la table système system.columns, ainsi que des colonnes qui ne sont pas prises en charge dans ClickHouse ou qui n’ont pas de sens (toujours NULL), mais qui sont requises par la norme.

Colonnes :

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

Exemple

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;

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:              ᴺᵁᴸᴸ

Contient les colonnes lues à partir de la table système system.databases, ainsi que des colonnes qui ne sont pas prises en charge dans ClickHouse ou qui n’ont pas de sens (toujours NULL), mais qui sont requises par la norme.

Colonnes :

• catalog_name (String) — Le nom de la base de données.
• schema_name (String) — Le nom de la base de données.
• schema_owner (String) — Nom du propriétaire du schéma, toujours 'default'.
• default_character_set_catalog (Nullable(String)) — NULL, non pris en charge.
• default_character_set_schema (Nullable(String)) — NULL, non pris en charge.
• default_character_set_name (Nullable(String)) — NULL, non pris en charge.
• sql_path (Nullable(String)) — NULL, non pris en charge.

Exemple

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;

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:                      ᴺᵁᴸᴸ

Contient les colonnes lues à partir de la table système system.tables.

Colonnes :

• table_catalog (String) — Le nom de la base de données dans laquelle se trouve la table.
• table_schema (String) — Le nom de la base de données dans laquelle se trouve la table.
• table_name (String) — Le nom de la table.
• table_type (String) — Le type de table. Valeurs possibles :
  • BASE TABLE
  • VIEW
  • FOREIGN TABLE
  • LOCAL TEMPORARY
  • SYSTEM VIEW
• table_rows (Nullable(UInt64)) — Le nombre total de lignes. NULL s'il n'a pas pu être déterminé.
• data_length (Nullable(UInt64)) — La taille des données sur disque. NULL si elle n'a pas pu être déterminée.
• index_length (Nullable(UInt64)) — La taille totale de la clé primaire, des index secondaires et de toutes les marks.
• table_collation (Nullable(String)) — La collation par défaut de la table. Toujours utf8mb4_0900_ai_ci.
• table_comment (Nullable(String)) — Le commentaire utilisé lors de la création de la table.

Exemple

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;

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

Contient les colonnes issues de la table système system.tables, lorsque le moteur de table View est utilisé.

Colonnes :

• table_catalog (String) — Nom de la base de données dans laquelle se trouve la table.
• table_schema (String) — Nom de la base de données dans laquelle se trouve la table.
• table_name (String) — Nom de la table.
• view_definition (String) — Requête SELECT de la vue.
• check_option (String) — NONE, aucune vérification.
• is_updatable (Enum8) — NO, la vue ne peut pas être mise à jour.
• is_insertable_into (Enum8) — Indique si la vue créée est matérialisée. Valeurs possibles :
  • NO — La vue créée n’est pas matérialisée.
  • YES — La vue créée est matérialisée.
• is_trigger_updatable (Enum8) — NO, le déclencheur ne peut pas être mis à jour.
• is_trigger_deletable (Enum8) — NO, le déclencheur ne peut pas être supprimé.
• is_trigger_insertable_into (Enum8) — NO, aucune donnée ne peut être insérée dans le déclencheur.

Exemple

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;

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

Contient les colonnes de la table système system.tables qui sont soumises à des contraintes.

Colonnes :

• constraint_catalog (String) — Actuellement inutilisé. Toujours def.
• constraint_schema (String) — Le nom du schéma (base de données) auquel appartient la contrainte.
• constraint_name (Nullable(String)) — Le nom de la contrainte.
• table_catalog (String) — Actuellement inutilisé. Toujours def.
• table_schema (String) — Le nom du schéma (base de données) auquel appartient la table.
• table_name (String) — Le nom de la table à laquelle la contrainte s'applique.
• column_name (Nullable(String)) — Le nom de la colonne à laquelle la contrainte s'applique.
• ordinal_position (UInt32) — Actuellement inutilisé. Toujours 1.
• position_in_unique_constraint (Nullable(UInt32)) — Actuellement inutilisé. Toujours NULL.
• referenced_table_schema (Nullable(String)) — Actuellement inutilisé. Toujours NULL.
• referenced_table_name (Nullable(String)) — Actuellement inutilisé. Toujours NULL.
• referenced_column_name (Nullable(String)) — Actuellement inutilisé. Toujours NULL.

Exemple

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;

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:        ᴺᵁᴸᴸ

Contient des informations sur les clés étrangères. Renvoie actuellement un résultat vide (aucune ligne), ce qui suffit à assurer la compatibilité avec des outils tiers comme Tableau Online.

Colonnes :

• constraint_catalog (String) — Actuellement inutilisé.
• constraint_schema (String) — Actuellement inutilisé.
• constraint_name (Nullable(String)) — Actuellement inutilisé.
• unique_constraint_catalog (String) — Actuellement inutilisé.
• unique_constraint_schema (String) — Actuellement inutilisé.
• unique_constraint_name (Nullable(String)) — Actuellement inutilisé.
• match_option (String) — Actuellement inutilisé.
• update_rule (String) — Actuellement inutilisé.
• delete_rule (String) — Actuellement inutilisé.
• table_name (String) — Actuellement inutilisé.
• referenced_table_name (String) — Actuellement inutilisé.

Fournit des informations sur les index de table. Renvoie actuellement un résultat vide (aucune ligne), ce qui suffit à assurer la compatibilité avec des outils tiers comme Tableau Online.

Fournit des informations sur les collations disponibles. Contient les collations de system.collations, ainsi que deux lignes de compatibilité MySQL : utf8mb4_0900_ai_ci — la collation annoncée par le protocole MySQL lors du handshake, dans SCHEMATA.DEFAULT_COLLATION_NAME et dans TABLES.TABLE_COLLATION — et binary — la pseudo-collation que le protocole MySQL attribue aux colonnes numériques et aux autres colonnes non textuelles dans les métadonnées du jeu de résultats — afin qu'un client compatible MySQL qui suit les collations annoncées jusqu'à cette vue puisse les y trouver.

Colonnes :

• collation_name (String) — Le nom de la collation.
• character_set_name (Nullable(String)) — Le jeu de caractères associé (utf8mb4 pour utf8mb4_0900_ai_ci, binary pour binary, sinon NULL). Fourni pour assurer la compatibilité avec MySQL.
• id (Nullable(UInt64)) — L'identifiant de la collation MySQL (255 pour utf8mb4_0900_ai_ci, 63 pour binary, sinon NULL). Fourni pour assurer la compatibilité avec MySQL.
• is_default (Nullable(String)) — Indique si la collation est celle par défaut de son jeu de caractères (Yes pour les deux lignes de compatibilité MySQL, sinon NULL). Fourni pour assurer la compatibilité avec MySQL.
• is_compiled (Nullable(String)) — Indique si la collation est compilée dans le serveur (Yes pour les deux lignes de compatibilité MySQL, sinon NULL). Fourni pour assurer la compatibilité avec MySQL.
• sortlen (Nullable(UInt64)) — La longueur minimale de tri (0 pour utf8mb4_0900_ai_ci, 1 pour binary, sinon NULL). Fourni pour assurer la compatibilité avec MySQL.
• pad_attribute (Nullable(String)) — L'attribut de remplissage, NO PAD ou PAD SPACE (NO PAD pour les deux lignes de compatibilité MySQL, sinon NULL). Fourni pour assurer la compatibilité avec MySQL.