system.columns

Contains information about columns in all the tables.

You can use this table to get information similar to the DESCRIBE TABLE query, but for multiple tables at once.

Columns from temporary tables are visible in the system.columns only in those session where they have been created. They are shown with the empty database field.

Columns:

  • database (String) — Database name.
  • table (String) — Table name.
  • name (String) — Column name.
  • type (String) — Column type.
  • position (UInt64) — Ordinal position of a column in a table starting with 1.
  • default_kind (String) — Expression type (DEFAULT, MATERIALIZED, ALIAS) for the default value, or an empty string if it is not defined.
  • default_expression (String) — Expression for the default value, or an empty string if it is not defined.
  • data_compressed_bytes (UInt64) — The size of compressed data, in bytes.
  • data_uncompressed_bytes (UInt64) — The size of decompressed data, in bytes.
  • marks_bytes (UInt64) — The size of marks, in bytes.
  • comment (String) — Comment on the column, or an empty string if it is not defined.
  • is_in_partition_key (UInt8) — Flag that indicates whether the column is in the partition expression.
  • is_in_sorting_key (UInt8) — Flag that indicates whether the column is in the sorting key expression.
  • is_in_primary_key (UInt8) — Flag that indicates whether the column is in the primary key expression.
  • is_in_sampling_key (UInt8) — Flag that indicates whether the column is in the sampling key expression.
  • compression_codec (String) — Compression codec name.

Example

  1. SELECT * FROM system.columns LIMIT 2 FORMAT Vertical;
  1. Row 1:
  2. ──────
  3. database:                system
  4. table:                   aggregate_function_combinators
  5. name:                    name
  6. type:                    String
  7. default_kind:
  8. default_expression:
  9. data_compressed_bytes:   0
  10. data_uncompressed_bytes: 0
  11. marks_bytes:             0
  12. comment:
  13. is_in_partition_key:     0
  14. is_in_sorting_key:       0
  15. is_in_primary_key:       0
  16. is_in_sampling_key:      0
  17. compression_codec:
  19. Row 2:
  20. ──────
  21. database:                system
  22. table:                   aggregate_function_combinators
  23. name:                    is_internal
  24. type:                    UInt8
  25. default_kind:
  26. default_expression:
  27. data_compressed_bytes:   0
  28. data_uncompressed_bytes: 0
  29. marks_bytes:             0
  30. comment:
  31. is_in_partition_key:     0
  32. is_in_sorting_key:       0
  33. is_in_primary_key:       0
  34. is_in_sampling_key:      0
  35. compression_codec:

The system.columns table contains the following columns (the column type is shown in brackets):

  • database (String) — Database name.
  • table (String) — Table name.
  • name (String) — Column name.
  • type (String) — Column type.
  • default_kind (String) — Expression type (DEFAULT, MATERIALIZED, ALIAS) for the default value, or an empty string if it is not defined.
  • default_expression (String) — Expression for the default value, or an empty string if it is not defined.
  • data_compressed_bytes (UInt64) — The size of compressed data, in bytes.
  • data_uncompressed_bytes (UInt64) — The size of decompressed data, in bytes.
  • marks_bytes (UInt64) — The size of marks, in bytes.
  • comment (String) — Comment on the column, or an empty string if it is not defined.
  • is_in_partition_key (UInt8) — Flag that indicates whether the column is in the partition expression.
  • is_in_sorting_key (UInt8) — Flag that indicates whether the column is in the sorting key expression.
  • is_in_primary_key (UInt8) — Flag that indicates whether the column is in the primary key expression.
  • is_in_sampling_key (UInt8) — Flag that indicates whether the column is in the sampling key expression.