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`
`POLICY_TAGS`	`ARRAY<STRING>`	The list of policy tags that are attached to the column

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.

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

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 | policy_tags |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL | 0 rows      |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL | 0 rows      |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | 0 rows      |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | 0 rows      |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL | 0 rows      |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | 0 rows      |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+