SEARCH_INDEXES view

The INFORMATION_SCHEMA.SEARCH_INDEXES view contains one row for each search index in a dataset.

Required permissions

To see search index metadata, you need the bigquery.tables.get or bigquery.tables.list Identity and Access Management (IAM) permission on the table with the index. Each of the following predefined IAM 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, see Access control with IAM .

Schema

When you query the INFORMATION_SCHEMA.SEARCH_INDEXES view, the query results contain one row for each search index in a dataset.

The INFORMATION_SCHEMA.SEARCH_INDEXES view has the following schema:

Column name

Data type

Value

index_catalog

STRING

The name of the project that contains the dataset.

index_schema

STRING

The name of the dataset that contains the index.

table_name

STRING

The name of the base table that the index is created on.

index_name

STRING

The name of the index.

index_status

STRING

The status of the index: ACTIVE ,

PENDING
          DISABLEMENT

, TEMPORARILY DISABLED , or PERMANENTLY DISABLED .

ACTIVE means that the index is usable or being created. Refer to the coverage_percentage to see the progress of index creation.
PENDING DISABLEMENT means that the total size of indexed base tables exceeds your organization's limit ; 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's limit , 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 from STRING to INT64 .

creation_time

TIMESTAMP

The time the index was created.

last_modification_time

TIMESTAMP

The last time the index configuration was modified. For example, deleting an indexed column.

last_refresh_time

TIMESTAMP

The last time the table data was indexed. A NULL value means the index is not yet available.

disable_time

TIMESTAMP

The time the status of the index was set to DISABLED . The value is NULL if the index status is not DISABLED .

disable_reason

STRING

The reason the index was disabled. NULL if the index status is not DISABLED .

DDL

STRING

The DDL statement used to create the index.

coverage_percentage

INTEGER

The approximate percentage of table data that has been indexed. 0% means the index is not usable in a SEARCH query, even if some data has already been indexed.

unindexed_row_count

INTEGER

The number of rows in the base table that have not been indexed.

total_logical_bytes

INTEGER

The number of billable logical bytes for the index.

total_storage_bytes

INTEGER

The number of billable storage bytes for the index.

analyzer

STRING

The text analyzer to use to generate tokens for the search index.

Scope and syntax

Queries against this view must have a dataset qualifier . The following table explains the region scope for this view:

View Name	Resource scope	Region scope
`[ PROJECT_ID .] DATASET_ID .INFORMATION_SCHEMA.SEARCH_INDEXES`	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.
DATASET_ID : the ID of your dataset. For more information, see Dataset qualifier .

Example

  -- Returns metadata for search indexes in a single dataset. 
 SELECT 
  
 * 
  
 FROM 
  
 myDataset 
 . 
 INFORMATION_SCHEMA 
 . 
 SEARCH_INDEXES 
 ;

Example

The following example shows all active search indexes on tables in the dataset my_dataset , located in the project my_project . It includes their names, the DDL statements used to create them, their coverage percentage, and their text analyzer. If an indexed base table is less than 10GB, then its index is not populated, in which case coverage_percentage is 0.

  SELECT 
  
 table_name 
 , 
  
 index_name 
 , 
  
 ddl 
 , 
  
 coverage_percentage 
 , 
  
 analyzer 
 FROM 
  
 my_project 
 . 
 my_dataset 
 . 
 INFORMATION_SCHEMA 
 . 
 SEARCH_INDEXES 
 WHERE 
  
 index_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   |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+