SEARCH_INDEX_OPTIONS view

TheINFORMATION_SCHEMA.SEARCH_INDEX_OPTIONS view contains one row for eachsearch index option 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_OPTIONS view, the queryresults contain one row for each search index option in a dataset.

TheINFORMATION_SCHEMA.SEARCH_INDEX_OPTIONS 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.
option_nameSTRING The name of the option, which can be one of the following:analyzer,analyzer_options,data_types, ordefault_index_column_granularity.
option_typeSTRINGThe type of the option.
option_valueSTRINGThe value of the option.
Note: If a search index option is not specified, a row containing the defaultsearch index option is produced by a query. Theanalyzer anddata_typesoptions are always populated in theSEARCH_INDEX_OPTIONS view regardless ofwhether they are specified in the DDL or not. If not specified, the defaultLOG_ANALYZER and["STRING"] values are respectively produced. Other optionsare populated in theSEARCH_INDEX_OPTIONS view only when they're specified inCREATE SEARCH INDEX DDL.

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_OPTIONSDataset 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 index options in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.SEARCH_INDEX_OPTIONS;

Example

The following example creates three search index options for columns oftable1 and then extracts those options from fields that are indexed:

CREATESEARCHINDEXmyIndexON`mydataset.table1`(ALLCOLUMNS)OPTIONS(analyzer='LOG_ANALYZER',analyzer_options='{ "delimiters" : [".", "-"] }',data_types=['STRING','INT64','TIMESTAMP']);SELECTindex_name,option_name,option_type,option_valueFROMmydataset.INFORMATION_SCHEMA.SEARCH_INDEX_OPTIONSWHEREtable_name='table1';

The result is similar to the following:

+------------+------------------+---------------+----------------------------------+| index_name |  option_name     | option_type   | option_value                     |+------------+------------------+---------------+----------------------------------+| myIndex    | analyzer         | STRING        | LOG_ANALYZER                     || myIndex    | analyzer_options | STRING        | { "delimiters": [".", "-"] }     || myIndex    | data_types       | ARRAY<STRING> | ["STRING", "INT64", "TIMESTAMP"] |+------------+------------------+---------------+----------------------------------+

The following example creates one search index option for columns oftable1and then extracts those options from fields that are indexed. If an optiondoesn't exist, the default option is produced:

CREATESEARCHINDEXmyIndexON`mydataset.table1`(ALLCOLUMNS)OPTIONS(analyzer='NO_OP_ANALYZER');SELECTindex_name,option_name,option_type,option_valueFROMmydataset.INFORMATION_SCHEMA.SEARCH_INDEX_OPTIONSWHEREtable_name='table1';

The result is similar to the following:

+------------+------------------+---------------+----------------+| index_name |  option_name     | option_type   | option_value   |+------------+------------------+---------------+----------------+| myIndex    | analyzer         | STRING        | NO_OP_ANALYZER || myIndex    | data_types       | ARRAY<STRING> | ["STRING"]     |+------------+------------------+---------------+----------------+

The following example creates no search index options for columns oftable1and then extracts the default options from fields that are indexed:

CREATESEARCHINDEXmyIndexON`mydataset.table1`(ALLCOLUMNS);SELECTindex_name,option_name,option_type,option_valueFROMmydataset.INFORMATION_SCHEMA.SEARCH_INDEX_OPTIONSWHEREtable_name='table1';

The result is similar to the following:

+------------+------------------+---------------+----------------+| index_name |  option_name     | option_type   | option_value   |+------------+------------------+---------------+----------------+| myIndex    | analyzer         | STRING        | LOG_ANALYZER   || myIndex    | data_types       | ARRAY<STRING> | ["STRING"]     |+------------+------------------+---------------+----------------+

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-19 UTC.