SEARCH_INDEXES view

TheINFORMATION_SCHEMA.SEARCH_INDEXES view contains one row for each searchindex 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_INDEXES view, the query resultscontain one row for each search index in a dataset.

TheINFORMATION_SCHEMA.SEARCH_INDEXES 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_statusSTRINGThe status of the index:ACTIVE,PENDING DISABLEMENT,TEMPORARILY DISABLED, orPERMANENTLY DISABLED.
  • ACTIVE means that the index is usable or being created. Refer to thecoverage_percentage to see the progress of index creation.
  • PENDING DISABLEMENT means that the total size of indexed base tables exceeds your organization'slimit; the index is queued for deletion. While in this state, the index is usable in search queries and you are charged for the search index storage.
  • TEMPORARILY DISABLED means that either the total size of indexed base tables exceeds your organization'slimit, or the base indexed table is smaller than 10GB. While in this state, the index is not used in search queries and you are not charged for the search index storage.
  • PERMANENTLY DISABLED means that there is an incompatible schema change on the base table, such as changing the type of an indexed column fromSTRING toINT64.
creation_timeTIMESTAMPThe time the index was created.
last_modification_timeTIMESTAMPThe last time the index configuration was modified. For example, deleting an indexed column.
last_refresh_timeTIMESTAMPThe last time the table data was indexed. ANULL value means the index is not yet available.
disable_timeTIMESTAMPThe time the status of the index was set toDISABLED. The value isNULL if the index status is notDISABLED.
disable_reasonSTRINGThe reason the index was disabled.NULL if the index status is notDISABLED.
DDLSTRINGThe DDL statement used to create the index.
coverage_percentageINTEGERThe approximate percentage of table data that has been indexed. 0% means the index is not usable in aSEARCH query, even if some data has already been indexed.
unindexed_row_countINTEGERThe number of rows in the base table that have not been indexed.
total_logical_bytesINTEGERThe number of billable logical bytes for the index.
total_storage_bytesINTEGERThe number of billable storage bytes for the index.
analyzerSTRINGThetext analyzer to use to generate tokens for the search index.

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_INDEXESDataset 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_INDEXES;

Example

The following example shows all active search indexes on tables in the datasetmy_dataset, located in the projectmy_project. It includes their names, theDDL statements used to create them, their coverage percentage, and theirtext analyzer. If an indexed base table isless than 10GB, then its index is not populated, in which casecoverage_percentage is 0.

SELECTtable_name,index_name,ddl,coverage_percentage,analyzerFROMmy_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXESWHEREindex_status='ACTIVE';

The results should look like the following:

+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+| table_name  | index_name  | ddl                                                                                  | coverage_percentage | analyzer       |+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+| small_table | names_index | CREATE SEARCH INDEX `names_index` ON `my_project.my_dataset.small_table`(names)      | 0                   | NO_OP_ANALYZER || large_table | logs_index  | CREATE SEARCH INDEX `logs_index` ON `my_project.my_dataset.large_table`(ALL COLUMNS) | 100                 | LOG_ANALYZER   |+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

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.