COLUMNS view

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

Required permissions

To query theINFORMATION_SCHEMA.COLUMNS view, you need the followingIdentity and Access Management (IAM) permissions:

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

Each of the following predefined IAM roles includes the precedingpermissions:

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

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.COLUMNS view, the query results containone row for each column (field) in a table.

TheINFORMATION_SCHEMA.COLUMNS view has the following schema:

Column nameData typeValue
table_catalogSTRINGThe project ID of the project that contains the dataset.
table_schemaSTRINGThe name of the dataset that contains the table also referred to as thedatasetId.
table_nameSTRINGThe name of the table or view also referred to as thetableId.
column_nameSTRINGThe name of the column.
ordinal_positionINT64The 1-indexed offset of the column within the table; if it's a pseudo column such as _PARTITIONTIME or _PARTITIONDATE, the value isNULL.
is_nullableSTRINGYES orNO depending on whether the column's mode allowsNULL values.
data_typeSTRINGThe column's GoogleSQLdata type.
is_generatedSTRINGThe value isALWAYS if the column is anautomatically generated embedding column; otherwise, the value isNEVER.
generation_expressionSTRINGThe value is the generation expression used to define the column if the column is an automatically generated embedding column; otherwise the value isNULL.
is_storedSTRINGThe value isYES if the column is an automatically generated embedding column; otherwise, the value isNULL.
is_hiddenSTRINGYES orNO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE.
is_updatableSTRINGThe value is alwaysNULL.
is_system_definedSTRINGYES orNO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE.
is_partitioning_columnSTRINGYES orNO depending on whether the column is apartitioning column.
clustering_ordinal_positionINT64The 1-indexed offset of the column within the table's clustering columns; the value isNULL if the table is not a clustered table.
collation_nameSTRING The name of thecollation specification if it exists; otherwise,NULL.

If aSTRING orARRAY<STRING> is passed in, the collation specification is returned if it exists; otherwiseNULL is returned.
column_defaultSTRING Thedefault value of the column if it exists; otherwise, the value isNULL.
rounding_modeSTRING The mode of rounding that's used for values written to the field if its type is a parameterizedNUMERIC orBIGNUMERIC; otherwise, the value isNULL.
data_policies.nameSTRING The list of data policies that are attached to the column to control access and masking. This field is in (Preview).
policy_tagsARRAY<STRING> The list of policy tags that are attached to the column.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include a dataset or a region qualifier. Forqueries 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 moreinformation seeSyntax.The following table explains the region and resource scopes for this view:

View nameResource scopeRegion scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.COLUMNSProject levelREGION
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.COLUMNSDataset levelDataset location
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: anydataset region name. For example,`region-us`.
  • DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

    Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

The following example retrieves metadata from theINFORMATION_SCHEMA.COLUMNSview for thepopulation_by_zip_2010 table in thecensus_bureau_usadataset. This dataset is part of the BigQuerypublic dataset program.

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

The following column is excluded from the query results:

  • IS_UPDATABLE
Note:INFORMATION_SCHEMA view names are case-sensitive.
SELECT*EXCEPT(is_updatable)FROM`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNSWHEREtable_name='population_by_zip_2010';

The result is similar to the following. For readability, some columnsare 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      |+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.