COLUMNS view

The INFORMATION_SCHEMA.COLUMNS view contains one row for each column (field) in a table.

Required permissions

To query the INFORMATION_SCHEMA.COLUMNS view, you need the following Identity and Access Management (IAM) permissions:

  • bigquery.tables.get
  • bigquery.tables.list

Each of the following predefined IAM roles includes the preceding permissions:

  • roles/bigquery.admin
  • roles/bigquery.dataViewer
  • roles/bigquery.dataEditor
  • roles/bigquery.metadataViewer

For more information about BigQuery permissions, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.COLUMNS view, the query results contain one row for each column (field) in a table.

The INFORMATION_SCHEMA.COLUMNS view has the following schema:

Column name Data type Value
TABLE_CATALOG STRING The project ID of the project that contains the dataset
TABLE_SCHEMA STRING The name of the dataset that contains the table also referred to as the datasetId
TABLE_NAME STRING The name of the table or view also referred to as the tableId
COLUMN_NAME STRING The name of the column
ORDINAL_POSITION INT64 The 1-indexed offset of the column within the table; if it's a pseudo column such as _PARTITIONTIME or _PARTITIONDATE, the value is NULL
IS_NULLABLE STRING YES or NO depending on whether the column's mode allows NULL values
DATA_TYPE STRING The column's GoogleSQL data type
IS_GENERATED STRING The value is always NEVER
GENERATION_EXPRESSION STRING The value is always NULL
IS_STORED STRING The value is always NULL
IS_HIDDEN STRING YES or NO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE
IS_UPDATABLE STRING The value is always NULL
IS_SYSTEM_DEFINED STRING YES or NO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE
IS_PARTITIONING_COLUMN STRING YES or NO depending on whether the column is a partitioning column
CLUSTERING_ORDINAL_POSITION INT64 The 1-indexed offset of the column within the table's clustering columns; the value is NULL if the table is not a clustered table
COLLATION_NAME STRING The name of the collation specification if it exists; otherwise, NULL

If a STRING or ARRAY<STRING> is passed in, the collation specification is returned if it exists; otherwise NULL is returned
COLUMN_DEFAULT STRING The default value of the column if it exists; otherwise, the value is NULL
ROUNDING_MODE STRING The mode of rounding that's used for values written to the field if its type is a parameterized NUMERIC or BIGNUMERIC; otherwise, the value is NULL

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For queries with a dataset qualifier, you must have permissions for the dataset. For queries with a region qualifier, you must have permissions for the project. For more information see Syntax. The following table explains the region and resource scopes for this view:

View name Resource scope Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.COLUMNS Project level REGION
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.COLUMNS Dataset level Dataset location
Replace the following:
  • Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: any dataset region name. For example, `region-us`.
  • DATASET_ID: the ID of your dataset. For more information, see Dataset qualifier.

Example

The following example retrieves metadata from the INFORMATION_SCHEMA.COLUMNS view for the population_by_zip_2010 table in the census_bureau_usa dataset. This dataset is part of the BigQuery public dataset program.

Because the table you're querying is in another project, the bigquery-public-data project, you add the project ID to the dataset in the following format: `project_id`.dataset.INFORMATION_SCHEMA.view; for example, `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

The following columns are excluded from the query results because they are currently reserved for future use:

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE
  SELECT     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)   FROM     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS   WHERE     table_name = 'population_by_zip_2010';

The result is similar to the following. For readability, some columns are excluded from the result.

 +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ |       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ | population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL | | population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL | | population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | | population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | | population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL | | population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+