SEARCH_INDEX_COLUMNS view

TheINFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view contains one row for eachsearch-indexed column on each table in a dataset.

Required permissions

To seesearch index metadata, you need thebigquery.tables.get orbigquery.tables.list Identity and Access Management (IAM)permission on the table with the index. Each of the following predefinedIAM roles includes at least one of these permissions:

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

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

Schema

When you query theINFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view, the queryresults contain one row for each indexed column on each table in a dataset.

TheINFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS view has the following schema:

Column nameData typeValue
index_catalogSTRINGThe name of the project that contains the dataset.
index_schemaSTRINGThe name of the dataset that contains the index.
table_nameSTRINGThe name of the base table that the index is created on.
index_nameSTRINGThe name of the index.
index_column_nameSTRINGThe name of the top-level indexed column.
index_field_pathSTRINGThe full path of the expanded indexed field, starting with the column name. Fields are separated by a period.

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 have adataset qualifier. Thefollowing table explains the region scope for this view:

View NameResource scopeRegion scope
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNSDataset levelDataset location
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

Example

-- Returns metadata for search indexes in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS;

Examples

The following example creates a search index on all columns ofmy_table.

CREATETABLEdataset.my_table(aSTRING,bINT64,cSTRUCT<dINT64,eARRAY<STRING>,fSTRUCT<gSTRING,hINT64>>)ASSELECT'hello'ASa,10ASb,(20,['x','y'],('z',30))ASc;CREATESEARCHINDEXmy_indexONdataset.my_table(ALLCOLUMNS);

The following query extracts information on which fields are indexed.Theindex_field_path indicates which field of a column isindexed. This differs from theindex_column_name only in the case of aSTRUCT, where the full path to the indexed field is given. In this example,columnc contains anARRAY<STRING> fielde and anotherSTRUCT calledf which contains aSTRING fieldg, each of which is indexed.

SELECTtable_name,index_name,index_column_name,index_field_pathFROMmy_project.dataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS

The result is similar to the following:

+------------+------------+-------------------+------------------+| table_name | index_name | index_column_name | index_field_path |+------------+------------+-------------------+------------------+| my_table   | my_index   | a                 | a                || my_table   | my_index   | c                 | c.e              || my_table   | my_index   | c                 | c.f.g            |+------------+------------+-------------------+------------------+

The following query joins theINFORMATION_SCHEMA.SEARCH_INDEX_COUMNS view withtheINFORMATION_SCHEMA.SEARCH_INDEXES andINFORMATION_SCHEMA.COLUMNS viewsto include the search index status and the data type of each column:

SELECTindex_columns_view.index_catalogASproject_name,index_columns_view.index_SCHEMAASdataset_name,indexes_view.TABLE_NAMEAStable_name,indexes_view.INDEX_NAMEASindex_name,indexes_view.INDEX_STATUSASstatus,index_columns_view.INDEX_COLUMN_NAMEAScolumn_name,index_columns_view.INDEX_FIELD_PATHASfield_path,columns_view.DATA_TYPEASdata_typeFROMmydataset.INFORMATION_SCHEMA.SEARCH_INDEXESindexes_viewINNERJOINmydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNSindex_columns_viewONindexes_view.TABLE_NAME=index_columns_view.TABLE_NAMEANDindexes_view.INDEX_NAME=index_columns_view.INDEX_NAMELEFTOUTERJOINmydataset.INFORMATION_SCHEMA.COLUMNScolumns_viewONindexes_view.INDEX_CATALOG=columns_view.TABLE_CATALOGANDindexes_view.INDEX_SCHEMA=columns_view.TABLE_SCHEMAANDindex_columns_view.TABLE_NAME=columns_view.TABLE_NAMEANDindex_columns_view.INDEX_COLUMN_NAME=columns_view.COLUMN_NAMEORDERBYproject_name,dataset_name,table_name,column_name;

The result is similar to the following:

+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+| project    | dataset    | table    | index_name | status | column_name | field_path | data_type                                                     |+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+| my_project | my_dataset | my_table | my_index   | ACTIVE | a           | a          | STRING                                                        || my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.e        | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> || my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.f.g      | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

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 2026-02-18 UTC.