Information schema for GoogleSQL-dialect databases

The information schema is a built-in schema that's common to every Spanner database. You can run SQL queries against tables in the INFORMATION_SCHEMA to fetch schema metadata for a database.

For example, the following query fetches the names of all user-defined tables in a database:

  SELECT
    table_name
  FROM
    information_schema.tables
  WHERE
    table_schema = ''

Fine-grained access control users see filtered results for some INFORMATION_SCHEMA tables depending on their database role. For more information, see About fine-grained access control.

Usage

INFORMATION_SCHEMA tables are available only through SQL interfaces, for example:

The executeQuery API
The gcloud spanner databases execute-sql command
The Spanner Studio page of a database in the Google Cloud console)

Other single read methods don't support INFORMATION_SCHEMA.

Some additional INFORMATION_SCHEMA usage notes:

Queries against the INFORMATION_SCHEMA can be used in a read-only transaction, but not in a read-write transaction.
Queries against the INFORMATION_SCHEMA can use strong, bounded staleness, or exact staleness timestamp bounds.
If you are using a PostgreSQL-dialect database, see Information schema for PostgreSQL-dialect databases.
If you are a fine-grained access control user, INFORMATION_SCHEMA tables are filtered to only show schema elements that you have access to.

Row filtering in information_schema tables

Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role (or to members of that role) can see all rows in all information_schema tables. For other principals, for some tables, rows are filtered based on the current database role. The table and view descriptions in the following sections indicate how row filtering is applied for each table and view.

Tables in the INFORMATION_SCHEMA

The following sections describe the tables in the INFORMATION_SCHEMA for GoogleSQL-dialect databases.

`SCHEMATA`

The INFORMATION_SCHEMA.SCHEMATA table lists the schemas in the database. These include the information schema and the named schemas, which contain the tables you define.

Column name	Type	Description
`CATALOG_NAME`	`STRING`	The name of the catalog. This column exists for compatibility with SQL-standard information schema tables. This column is always an empty string.
`SCHEMA_NAME`	`STRING`	The name of the schema. This is a name for named schemas or `` for the default schema.
`PROTO_BUNDLE`	`STRING`	If the database contains proto bundle statements, this column provides information about the proto bundle used in the schema. This column is NULL if no proto bundle exists in the database.

`DATABASE_OPTIONS`

This table lists the options that are set on the database.

Column name	Type	Description
`CATALOG_NAME`	`STRING`	The name of the catalog. Always an empty string.
`SCHEMA_NAME`	`STRING`	The name of the schema. An empty string if unnamed.
`OPTION_NAME`	`STRING`	The name of the database option.
`OPTION_TYPE`	`STRING`	The data type of the database option.
`OPTION_VALUE`	`STRING`	The database option value.

`PLACEMENTS`

This table lists the placements in the database.

Column name	Type	Description
`PLACEMENT_NAME`	`STRING`	The name of the placement.
`IS_DEFAULT`	`BOOL`	A boolean that indicates whether the placement is the default placement.

`PLACEMENT_OPTIONS`

For each placement, this table lists the options that are set on the placement in the OPTIONS clause of the CREATE PLACEMENT statement.

The valid values for OPTION_NAME include:

instance_partition
default_leader

Column name	Type	Description
`PLACEMENT_NAME`	`STRING`	The name of the placement.
`OPTION_NAME`	`STRING`	The name of the placement option.
`OPTION_TYPE`	`STRING`	The data type of the placement option. For both options, this is `STRING(MAX)`.
`OPTION_VALUE`	`STRING`	The value of the placement option. For `instance_partition`, this is the name of the instance partition. For `default_leader`, it is the name of the default leader region.

`TABLES`

This row-filtered table lists the tables and views in the database. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only tables that meet either of the following requirements:

The SELECT, INSERT, UPDATE, or DELETE fine-grained access control privileges are granted on the table to the current database role, to roles of which the current database role is a member, or to public.
The SELECT, INSERT, or UPDATE privileges are granted on any table column to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	Not used. Always an empty string.
`TABLE_SCHEMA`	`STRING`	Not used. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table, view, or synonym.
`TABLE_TYPE`	`STRING`	The type of the table. For tables it has the value `BASE TABLE`; for views it has the value `VIEW`; for synonyms, it has the value `SYNONYM`.
`PARENT_TABLE_NAME`	`STRING`	The name of the parent table if this table is interleaved, and `NULL` otherwise.
`ON_DELETE_ACTION`	`STRING`	This is set to `CASCADE` or `NO ACTION` for interleaved tables, and `NULL` otherwise. See TABLE statements for more information.
`SPANNER_STATE`	`STRING`	A table can go through multiple states during creation, if bulk operations are involved. For example, when the table is created with a foreign key that requires backfilling of its indexes. Possible states are: `ADDING_FOREIGN_KEY`: Adding the table's foreign keys. `WAITING_FOR_COMMIT`: Finalizing the schema change. `COMMITTED`: The schema change to create the table has been committed. You can't write to the table until the change is committed.
`INTERLEAVE_TYPE`	`STRING`	The expression text that indicates whether there is a parent-child relationship between this table and the table it is interleaved in. Possible values are: `IN`: The table doesn't have a parent-child relationship. A row in this table can exist regardless of the existence of its parent table row. `IN PARENT`: The table has a parent-child relationship. A row in this table requires the existence of its parent table row. An empty string indicates that this table has no interleaving relationships.
`ROW_DELETION_POLICY_EXPRESSION`	`STRING`	The expression text that defines the row deletion policy of the table. For example, `OLDER_THAN(CreatedAt, INTERVAL 1 DAY)` or `OLDER_THAN(ExpiredDate, INTERVAL 0 DAY)`.

`COLUMNS`

This row-filtered table lists the columns in a table. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only columns that meet either of the following requirements:

Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are directly granted on the column to the current database role, to roles of which the current database role is a member, or to public.
Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are granted on the table that contains the column to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	Not used. Always an empty string.
`TABLE_SCHEMA`	`STRING`	Not used. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table.
`COLUMN_NAME`	`STRING`	The name of the column.
`ORDINAL_POSITION`	`INT64`	The ordinal position of the column in the table, starting with a value of 1.
`COLUMN_DEFAULT`	`STRING`	A string representation of the SQL expression for the default value of the column. `NULL` if the column has no default value. Note: Prior to March 2022, `COLUMN_DEFAULT` used type `BYTES`.
`DATA_TYPE`	`STRING`	Included to satisfy the SQL standard. Always `NULL`. See the column `SPANNER_TYPE`
`IS_NULLABLE`	`STRING`	A string that indicates whether the column is nullable. In accordance with the SQL standard, the string is either `YES` or `NO`, rather than a Boolean value.
`SPANNER_TYPE`	`STRING`	The data type of the column.
`IS_GENERATED`	`STRING`	A string that indicates whether the column is generated. The string is either `ALWAYS` for a generated column or `NEVER` for a non-generated column.
`GENERATION_EXPRESSION`	`STRING`	A string representing the SQL expression of a generated column. `NULL` if the column is not a generated column.
`IS_STORED`	`STRING`	A string that indicates whether the generated column is stored. The string is always `YES` for generated columns, and `NULL` for non-generated columns.
`SPANNER_STATE`	`STRING`	The current state of the column. A new stored generated column added to an existing table may go through multiple user-observable states before it is fully usable. Possible values are: `WRITE_ONLY`: The column is being backfilled. No read is allowed. `COMMITTED`: The column is fully usable.

`COLUMN_PRIVILEGES`

This row-filtered table lists all the privileges granted at the column-level to any database role, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see privileges only for columns that meet either of the following requirements:

Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are directly granted on the column to the current database role, to roles of which the current database role is a member, or to public.
Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are granted on the table that contains the column to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	Not used. Always an empty string.
`TABLE_SCHEMA`	`STRING`	Not used. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table that contains the privileged column.
`COLUMN_NAME`	`STRING`	The name of the privileged column.
`PRIVILEGE_TYPE`	`STRING`	`SELECT`, `INSERT`, `UPDATE`
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.

`TABLE_PRIVILEGES`

This row-filtered table lists all the privileges granted at the table-level to database roles, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see privileges only for tables on which any of the SELECT, INSERT, UPDATE, or DELETE fine-grained access control privileges are granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	Not used. Always an empty string.
`TABLE_SCHEMA`	`STRING`	Not used. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table on which fine-grained access control privileges are granted.
`PRIVILEGE_TYPE`	`STRING`	One of `SELECT`, `INSERT`, `UPDATE`, and `DELETE`
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.

`TABLE_CONSTRAINTS`

This table contains one row for each constraint defined for the tables in the database.

Column name	Type	Description
`CONSTRAINT_CATALOG`	`STRING`	Always an emptry string.
`CONSTRAINT_SCHEMA`	`STRING`	The name of the constraint's schema. An empty string if unnamed.
`CONSTRAINT_NAME`	`STRING`	The name of the constraint.
`TABLE_CATALOG`	`STRING`	The name of constrained table's catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of constrained table's schema. An empty string if unnamed.
`TABLE_NAME`	`STRING`	The name of the constrained table.
`CONSTRAINT_TYPE`	`STRING`	The type of the constraint. Possible values are: `PRIMARY KEY` `FOREIGN KEY` `PLACEMENT KEY` `CHECK` `UNIQUE`
`IS_DEFERRABLE`	`STRING`	Always `NO`.
`INITIALLY_DEFERRED`	`STRING`	Always `NO`.
`ENFORCED`	`STRING`	Always `YES`.

`CONSTRAINT_TABLE_USAGE`

This table lists tables that define or are used by constraints. Includes tables that define PRIMARY KEY and UNIQUE constraints. Also includes the referenced tables of FOREIGN KEY definitions.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	The name of the constrained table's catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the constrained table's schema. An empty string if unnamed.
`TABLE_NAME`	`STRING`	The name of the constrained table.
`CONSTRAINT_CATALOG`	`STRING`	The name of the constraint's catalog. Always an empty string.
`CONSTRAINT_SCHEMA`	`STRING`	The name of the constraint's schema. An empty string if unnamed.
`CONSTRAINT_NAME`	`STRING`	The name of the constraint.

`REFERENTIAL_CONSTRAINTS`

This table contains one row about each FOREIGN KEY constraint.

Column name	Type	Description
`CONSTRAINT_CATALOG`	`STRING`	The name of the FOREIGN KEY's catalog. Always an empty string.
`CONSTRAINT_SCHEMA`	`STRING`	The name of the FOREIGN KEY's schema. An empty string if unnamed.
`CONSTRAINT_NAME`	`STRING`	The name of the FOREIGN KEY.
`UNIQUE_CONSTRAINT_CATALOG`	`STRING`	The catalog name of the PRIMARY KEY or UNIQUE constraint the FOREIGN KEY references. Always an empty string.
`UNIQUE_CONSTRAINT_SCHEMA`	`STRING`	The schema name of the PRIMARY KEY or UNIQUE constraint the FOREIGN KEY references. An empty string if unnamed.
`UNIQUE_CONSTRAINT_NAME`	`STRING`	The name of the PRIMARY KEY or UNIQUE constraint the FOREIGN KEY references.
`MATCH_OPTION`	`STRING`	Always `SIMPLE`.
`UPDATE_RULE`	`STRING`	Always `NO ACTION`.
`DELETE_RULE`	`STRING`	Either `CASCADE` or `NO ACTION`.
`SPANNER_STATE`	`STRING`	The current state of the foreign key. Spanner does not begin enforcing the constraint until the foreign key's backing indexes are created and backfilled. Once the indexes are ready, Spanner begins enforcing the constraint for new transactions while it validates the existing data. Possible values and the states they represent are: `BACKFILLING_INDEXES`: indexes are being backfilled. `VALIDATING_DATA`: existing data and new writes are being validated. `WAITING_FOR_COMMIT`: the foreign key bulk operations have completed successfully, or none were needed, but the foreign key is still pending. `COMMITTED`: the schema change was committed.

`CHECK_CONSTRAINTS`

The information_schema.CHECK_CONSTRAINTS table contains one row about each CHECK constraint defined by either the CHECK or the NOT NULL keyword.

Column name	Type	Description
`CONSTRAINT_CATALOG`	`STRING`	The name of the constraint's catalog. This column is never null, but always an empty string.
`CONSTRAINT_SCHEMA`	`STRING`	The name of the constraint's schema. An empty string if unnamed.
`CONSTRAINT_NAME`	`STRING`	The name of the constraint. This column is never null. If not explicitly specified in the schema definition, a system-defined name is assigned.
`CHECK_CLAUSE`	`STRING`	The expressions of the `CHECK` constraint. This column is never null.
`SPANNER_STATE`	`STRING`	The current state of the `CHECK` constraint. This column is never null. The possible states are as follows: `VALIDATING`: Spanner is validating the existing data. `COMMITTED`: There is no active schema change for this constraint.

`KEY_COLUMN_USAGE`

This row-filtered table contains one row about each column of the tables from TABLE_CONSTRAINTS that are constrained as keys by a PRIMARY KEY, FOREIGN KEY or UNIQUE constraint. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only columns that meet the following criteria:

Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are directly granted on the column to the current database role, to roles of which the current database role is a member, or to public.
Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are granted on the table that contains the column to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`CONSTRAINT_CATALOG`	`STRING`	The name of the constraint's catalog. Always an empty string.
`CONSTRAINT_SCHEMA`	`STRING`	The name of the constraint's schema. This column is never null. An empty string if unnamed.
`CONSTRAINT_NAME`	`STRING`	The name of the constraint.
`TABLE_CATALOG`	`STRING`	The name of the constrained column's catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the constrained column's schema. This column is never null. An empty string if unnamed.
`TABLE_NAME`	`STRING`	The name of the constrained column's table.
`COLUMN_NAME`	`STRING`	The name of the column.
`ORDINAL_POSITION`	`INT64`	The ordinal position of the column within the constraint's key, starting with a value of `1`.
`POSITION_IN_UNIQUE_CONSTRAINT`	`INT64`	For `FOREIGN KEY`s, the ordinal position of the column within the unique constraint, starting with a value of `1`. This column is null for other constraint types.

`CONSTRAINT_COLUMN_USAGE`

This table contains one row about each column used by a constraint. Includes the PRIMARY KEY and UNIQUE columns, plus the referenced columns of FOREIGN KEY constraints.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	The name of the column table's catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the column table's schema. This column is never null. An empty string if unnamed.
`TABLE_NAME`	`STRING`	The name of the column's table.
`COLUMN_NAME`	`STRING`	The name of the column that is used by the constraint.
`CONSTRAINT_CATALOG`	`STRING`	The name of the constraint's catalog. Always an empty string.
`CONSTRAINT_SCHEMA`	`STRING`	The name of the constraint's schema. An empty string if unnamed.
`CONSTRAINT_NAME`	`STRING`	The name of the constraint.

`TABLE_SYNONYMS`

This table lists lists synonym information for the table.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	Not used. Always an empty string.
`TABLE_SCHEMA`	`STRING`	Not used. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table.
`SYNONYM_CATALOG`	`STRING`	The name of the catalog for the synonym.
`SYNONYM_SCHEMA`	`STRING`	The name of the schema for the synonym.
`SYNONYM_TABLE_NAME`	`STRING`	The name of the table for the synonym.

`INDEXES`

This row-filtered table lists the indexes in the database. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only indexes that meet either of the following requirements:

Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are granted at the column level on all columns in the index to the current database role, to roles of which the current database role is a member, or to public.
Any of the SELECT, INSERT, UPDATE, or DELETE fine-grained access control privileges are granted on the table that has the index to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the schema. An empty string if unnamed.
`TABLE_NAME`	`STRING`	The name of the table.
`INDEX_NAME`	`STRING`	The name of the index. Tables with a `PRIMARY KEY` specification have a pseudo-index entry generated with the name `PRIMARY_KEY`, which allows the fields of the primary key to be determined.
`INDEX_TYPE`	`STRING`	The type of the index. The type is `INDEX` or `PRIMARY_KEY`.
`PARENT_TABLE_NAME`	`STRING`	Secondary indexes can be interleaved in a parent table, as discussed in Creating a secondary index. This column holds the name of that parent table, or an empty string if the index is not interleaved.
`IS_UNIQUE`	`BOOL`	Whether the index keys must be unique.
`IS_NULL_FILTERED`	`BOOL`	Whether the index includes entries with `NULL` values.
`INDEX_STATE`	`STRING`	The current state of the index. Possible values and the states they represent are: `PREPARE`: creating empty tables for a new index. `WRITE_ONLY`: backfilling data for a new index. `WRITE_ONLY_CLEANUP`: cleaning up a new index. `WRITE_ONLY_VALIDATE_UNIQUE`: checking uniqueness of data in a new index. `READ_WRITE`: normal index operation.
`SPANNER_IS_MANAGED`	`BOOL`	`TRUE` if the index is managed by Spanner; Otherwise, `FALSE`. Secondary backing indexes for foreign keys are managed by Spanner.

`INDEX_COLUMNS`

This row-filtered table lists the columns in an index. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only indexes that meet either of the following requirements:

Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are granted at the column level on all columns in the index to the current database role, to roles of which the current database role is a member, or to public.
Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are granted on the table that has index to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the schema. An empty string if unnamed.
`TABLE_NAME`	`STRING`	The name of the table.
`INDEX_NAME`	`STRING`	The name of the index.
`COLUMN_NAME`	`STRING`	The name of the column.
`ORDINAL_POSITION`	`INT64`	The ordinal position of the column in the index (or primary key), starting with a value of 1. This value is `NULL` for non-key columns (for example, columns specified in the `STORING` clause of an index).
`COLUMN_ORDERING`	`STRING`	The ordering of the column. The value is `ASC` or `DESC` for key columns, and `NULL` for non-key columns (for example, columns specified in the `STORING` clause of an index).
`IS_NULLABLE`	`STRING`	A string that indicates whether the column is nullable. In accordance with the SQL standard, the string is either `YES` or `NO`, rather than a Boolean value.
`SPANNER_TYPE`	`STRING`	The data type of the column.

`COLUMN_OPTIONS`

This row-filtered table lists lists the column options in a table. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see options only for columns that meet either of the following requirements:

Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are directly granted on the column to the current database role, to roles of which the current database role is a member, or to public.
Any of the SELECT, INSERT, or UPDATE fine-grained access control privileges are granted on the table that contains the column to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the schema. The name is empty for the default schema and non-empty for other schemas (for example, the `INFORMATION_SCHEMA` itself). This column is never null.
`TABLE_NAME`	`STRING`	The name of the table.
`COLUMN_NAME`	`STRING`	The name of the column.
`OPTION_NAME`	`STRING`	A SQL identifier that uniquely identifies the option. This identifier is the key of the `OPTIONS` clause in DDL.
`OPTION_TYPE`	`STRING`	A data type name that is the type of this option value.
`OPTION_VALUE`	`STRING`	A SQL literal describing the value of this option. The value of this column must be parsable as part of a query. The expression resulting from parsing the value must be castable to `OPTION_TYPE`. This column is never null.

`SEQUENCES`

This table lists the sequences metadata. SEQUENCES is row-filtered based on fine-grained access privileges, if a user with fine-grained access privileges is querying it.

Column name	Type	Description
`CATALOG`	`STRING`	The name of the catalog containing the sequence.
`SCHEMA`	`STRING`	The name of the schema containing the sequence.
`NAME`	`STRING`	The name of the sequence.
`DATA_TYPE`	`STRING`	The type of the sequence values. It uses the `INT64` data type.

`SEQUENCE_OPTIONS`

This table contains the configuration options for sequences. SEQUENCE_OPTIONS is row-filtered based on fine-grained access privileges, if a user with fine-grained access privileges is querying it.

Column name	Type	Description
`CATALOG`	`STRING`	The name of the catalog containing the sequence.
`SCHEMA`	`STRING`	The name of the schema containing the sequence.
`NAME`	`STRING`	The name of the sequence.
`OPTION_NAME`	`STRING`	The name of the sequence option.
`OPTION_TYPE`	`STRING`	A data type name that is the type of this option value.
`OPTION_VALUE`	`STRING`	The sequence option value. The expression that results from parsing the value must permit casting to `OPTION_TYPE`.

`SPANNER_STATISTICS`

This table lists the available query optimizer statistics packages.

Column name	Type	Description
`CATALOG_NAME`	`STRING`	The name of the catalog. Always an empty string.
`SCHEMA_NAME`	`STRING`	The name of the schema. The name is empty for the default schema and non-empty for other schemas (for example, the `INFORMATION_SCHEMA` itself). This column is never null.
`PACKAGE_NAME`	`STRING`	The name of the statistics package.
`ALLOW_GC`	`BOOL`	`FALSE` if the statistics package is exempted from garbage collection; Otherwise, `TRUE`. This attribute must be set to `FALSE` in order to reference the statistics package in a hint or through client API.

`VIEWS`

This row-filtered table lists the views in the database. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only views on which the SELECT fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`TABLE_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the schema. An empty string if unnamed.
`TABLE_NAME`	`STRING`	The name of the view.
`VIEW_DEFINITION`	`STRING`	The SQL text of the query that defines the view.
`SECURITY_TYPE`	`STRING`	The security type of the view. Either `INVOKER` or `DEFINER`. For more information, see About views.

`ROLES`

This row-filtered table lists the defined database roles for fine-grained access control, including system roles. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all database roles. All other principals can see only database roles to which they have been granted access either directly or through inheritance.

Column name	Type	Description
`ROLE_NAME`	`STRING`	The name of the database role.
`IS_SYSTEM`	`BOOL`	`TRUE` if the database role is a system role; `FALSE` otherwise.

`ROLE_GRANTEES`

This row-filtered table lists all role memberships explicitly granted to all database roles. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only the role memberships granted to the current database role or to a role of which the current database role is a member.

Because all database roles are members of the public role, the results omit records for implicit membership in the public role.

Column name	Type	Description
`ROLE_NAME`	`STRING`	The name of the database role in which this membership is granted.
`GRANTEE`	`STRING`	The name of the database role to which this membership is granted.

`CHANGE_STREAMS`

This row-filtered table lists all of a database's change streams, and notes which ones track the entire database versus specific tables or columns. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only change streams on which the SELECT fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`CHANGE_STREAM_CATALOG`	`STRING`	The name of the change stream's catalog. Always an empty string.
`CHANGE_STREAM_SCHEMA`	`STRING`	The name of this change stream's schema. Always an empty string.
`CHANGE_STREAM_NAME`	`STRING`	The name of the change stream.
`ALL`	`BOOL`	`TRUE` if this change stream tracks the entire database. `FALSE` if this change stream tracks specific tables or columns.

`CHANGE_STREAM_TABLES`

This row-filtered table contains information about tables and the change streams that watch them. Each row describes one table and one change stream. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only rows for change streams on which the SELECT privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

The data in CHANGE_STREAM_TABLES does not include the implicit relationships between tables and change streams that track the entire database.

Column name	Type	Description
`CHANGE_STREAM_CATALOG`	`STRING`	The name of the change stream's catalog. Always an empty string.
`CHANGE_STREAM_SCHEMA`	`STRING`	The name of the change stream's schema. Always an empty string.
`CHANGE_STREAM_NAME`	`STRING`	The name of the change stream that this row refers to.
`TABLE_CATALOG`	`STRING`	The name of the table's catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the table's schema. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table that this row refers to.
`ALL_COLUMNS`	`BOOL`	`TRUE` if this row's change stream tracks the entirety of the table this row refers to. Otherwise, `FALSE`.

`CHANGE_STREAM_COLUMNS`

This row-filtered table contains information about table columns and the change streams that watch them. Each row describes one change stream and one column. If a change stream tracks an entire table, then the columns in that table don't show in this view.

Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only rows for change streams on which the SELECT privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`CHANGE_STREAM_CATALOG`	`STRING`	The name of the change stream's catalog. Always an empty string.
`CHANGE_STREAM_SCHEMA`	`STRING`	The name of the change stream's schema. Always an empty string.
`CHANGE_STREAM_NAME`	`STRING`	The name of the change stream.
`TABLE_CATALOG`	`STRING`	The name of the table's catalog. Always an empty string.
`TABLE_SCHEMA`	`STRING`	The name of the table's schema. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table that this row refers to.
`COLUMN_NAME`	`STRING`	The name of the column that this row refers to.

`CHANGE_STREAM_OPTIONS`

This row-filtered table contains the configuration options for change streams. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only options for change streams on which the SELECT privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`CHANGE_STREAM_CATALOG`	`STRING`	The name of the change stream's catalog. Always an empty string.
`CHANGE_STREAM_SCHEMA`	`STRING`	The name of the change stream's schema. Always an empty string.
`CHANGE_STREAM_NAME`	`STRING`	The name of the change stream.
`OPTION_NAME`	`STRING`	The name of the change stream option.
`OPTION_TYPE`	`STRING`	The data type of the change stream option.
`OPTION_VALUE`	`STRING`	The change stream option value.

`CHANGE_STREAM_PRIVILEGES`

This row-filtered table lists all fine-grained access control privileges granted on all change streams to any database role, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change streams to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`CHANGE_STREAM_CATALOG`	`STRING`	The name of the catalog containing the change stream (an empty string).
`CHANGE_STREAM_SCHEMA`	`STRING`	The name of the schema containing the change stream (an empty string).
`CHANGE_STREAM_NAME`	`STRING`	The name of the change stream.
`PRIVILEGE_TYPE`	`STRING`	`SELECT` (the only privilege allowed for change streams).
`GRANTEE`	`STRING`	The name of database role to which this privilege is granted.

`ROUTINES`

This row-filtered table lists all of a database's change stream read functions. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only change stream read functions on which the EXECUTE fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`SPECIFIC_CATALOG`	`STRING`	The name of the routine's catalog. Always an empty string.
`SPECIFIC_SCHEMA`	`STRING`	The name of the routine's schema. Always an empty string.
`SPECIFIC_NAME`	`STRING`	The name of the routine. Uniquely identifies the routine even if its name is overloaded.
`ROUTINE_CATALOG`	`STRING`	The name of the routine's catalog. Always an empty string.
`ROUTINE_SCHEMA`	`STRING`	The name of the routine's schema. Always an empty string.
`ROUTINE_NAME`	`STRING`	The name of the routine. (Might be duplicated in case of overloading.)
`ROUTINE_TYPE`	`STRING`	The type of the routine (`FUNCTION` or `PROCEDURE`). Always `FUNCTION`
`DATA_TYPE`	`STRING`	The data type that the routine returns
`ROUTINE_BODY`	`STRING`	The type of the routine body (`SQL` or `EXTERNAL`).
`ROUTINE_DEFINITION`	`STRING`	The definition for the `ROUTINE_BODY`.
`SECURITY_TYPE`	`STRING`	The security type of the routine. Always `INVOKER`.

`ROUTINE_OPTIONS`

This row-filtered table contains one row for each option for each defined change stream read function.

Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only options for change stream read functions on which the EXECUTE fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`SPECIFIC_CATALOG`	`STRING`	The name of the routine's catalog. Always an empty string.
`SPECIFIC_SCHEMA`	`STRING`	The name of the routine's schema. Always an empty string.
`SPECIFIC_NAME`	`STRING`	The name of the routine. Uniquely identifies the routine in case of name overloading.
`OPTION_NAME`	`STRING`	A SQL identifier that uniquely identifies the option.
`OPTION_TYPE`	`STRING`	The data type of `OPTION_VALUE`.
`OPTION_VALUE`	`STRING`	A SQL literal describing the value of this option. The value of this column must be parsable as part of a query.

`PARAMETERS`

This row-filtered table defines the arguments for each change stream read function. Each row describes one argument for one change stream read function.

Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only parameters for change stream read functions on which the EXECUTE fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`SPECIFIC_CATALOG`	`STRING`	The name of the routine's catalog. Always an empty string.
`SPECIFIC_SCHEMA`	`STRING`	The name of the routine's schema. Always an empty string.
`SPECIFIC_NAME`	`STRING`	The name of the routine. Uniquely identifies the routine in case of name overloading.
`ORDINAL_POSITION`	`INT64`	The ordinal position of the parameter in the routine, starting with a value of 1.
`PARAMETER_NAME`	`STRING`	The name of the parameter.
`DATA_TYPE`	`STRING`	The data type of the parameter.

`ROUTINE_PRIVILEGES`

This row-filtered table lists all fine-grained access control privileges granted on all change stream read functions to any database role, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change stream read functions to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`SPECIFIC_CATALOG`	`STRING`	The name of the routine's catalog. Always an empty string.
`SPECIFIC_SCHEMA`	`STRING`	The name of the routine's schema. Always an empty string.
`SPECIFIC_NAME`	`STRING`	The name of the routine. Uniquely identifies the routine in case of name overloading.
`PRIVILEGE_TYPE`	`STRING`	Always `EXECUTE`.
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.

`ROLE_TABLE_GRANTS`

This row-filtered table lists all fine-grained access control privileges granted on all tables and views to any database role, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on tables and views to the current database role and to roles of which the current database role is a member, not including public.

Column name	Type	Description
`GRANTOR`	`STRING`	Not used. Always `NULL`.
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.
`TABLE_CATALOG`	`STRING`	Not used. Always an empty string.
`TABLE_SCHEMA`	`STRING`	Not used. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table or view.
`PRIVILEGE_TYPE`	`STRING`	The type of the privilege (`SELECT`, `INSERT`, `UPDATE`, or `DELETE`).
`IS_GRANTABLE`	`STRING`	Not used. Always `NO`.

`ROLE_COLUMN_GRANTS`

This row-filtered table lists all fine-grained access control privileges granted on all columns to any database role, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on columns to the current database role and to roles of which the current database role is a member, not including public.

The view includes the SELECT, INSERT, and UPDATE privileges that the column inherits from the table or view that contains the column.

Column name	Type	Description
`GRANTOR`	`STRING`	Not used. Always `NULL`.
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.
`TABLE_CATALOG`	`STRING`	Not used. Always an empty string.
`TABLE_SCHEMA`	`STRING`	Not used. Always an empty string.
`TABLE_NAME`	`STRING`	The name of the table or view that contains the column.
`COLUMN_NAME`	`STRING`	The name of the column on which the privilege is granted.
`PRIVILEGE_TYPE`	`STRING`	The type of the privilege (`SELECT`, `INSERT`, or `UPDATE`).
`IS_GRANTABLE`	`STRING`	Not used. Always `NO`.

`ROLE_CHANGE_STREAM_GRANTS`

This row-filtered table lists the SELECT privileges granted on all change streams to any database role, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change streams to the current database role and to roles of which the current database role is a member, not including public.

Column name	Type	Description
`CHANGE_STREAM_CATALOG`	`STRING`	Not used. Always an empty string.
`CHANGE_STREAM_SCHEMA`	`STRING`	The name of the schema that contains the change stream.
`CHANGE_STREAM_NAME`	`STRING`	The name of the change stream.
`PRIVILEGE_TYPE`	`STRING`	The type of the privilege (`SELECT` only).
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.

`ROLE_MODEL_GRANTS`

This row-filtered table lists all fine-grained access control privileges granted on all models to any database role, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on tables and views to the current database role and to roles of which the current database role is a member, not including public.

Column name	Type	Description
`GRANTOR`	`STRING`	Not used. Always `NULL`.
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.
`MODEL_CATALOG`	`STRING`	Not used. Always an empty string.
`MODEL_SCHEMA`	`STRING`	Not used. Always an empty string.
`MODEL_NAME`	`STRING`	The name of the model.
`PRIVILEGE_TYPE`	`STRING`	The type of the privilege (`EXECUTE`).
`IS_GRANTABLE`	`STRING`	Not used. Always `NO`.

`ROLE_ROUTINE_GRANTS`

This row-filtered table lists the EXECUTE privileges granted on all change stream read functions to any database role, including public. Principals with IAM database-level permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see only privileges granted on change stream read functions to the current database role and to roles of which the current database role is a member, not including public.

Column name	Type	Description
`GRANTOR`	`STRING`	Not used. Always `NULL`.
`GRANTEE`	`STRING`	The name of the role that the privilege is granted to.
`SPECIFIC_CATALOG`	`STRING`	The name of the routine catalog.
`SPECIFIC_SCHEMA`	`STRING`	The name of the routine schema.
`SPECIFIC_NAME`	`STRING`	The name of the routine. Uniquely identifies the routine in case of name overloading.
`PRIVILEGE_TYPE`	`STRING`	The type of the privilege granted. Always `EXECUTE`.
`IS_GRANTABLE`	`STRING`	Not used. Always `NO`.

`MODELS`

This table lists all of a database's models.

Column name	Type	Description
`MODEL_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`MODEL_SCHEMA`	`STRING`	The name of this model's schema. Always an empty string.
`MODEL_NAME`	`STRING`	The name of the model.
`IS_REMOTE`	`BOOL`	`TRUE` if this is a remote model. `FALSE` if this is a managed model.

`MODEL_OPTIONS`

This table contains the configuration options for models.

Column name	Type	Description
`MODEL_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`MODEL_SCHEMA`	`STRING`	The name of this model's schema. Always an empty string.
`MODEL_NAME`	`STRING`	The name of the model.
`OPTION_NAME`	`STRING`	The name of the model option.
`OPTION_TYPE`	`STRING`	The data type of the model option.
`OPTION_VALUE`	`STRING`	The model option value.

`MODEL_COLUMNS`

This table lists the columns in a model.

Column name	Type	Description
`MODEL_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`MODEL_SCHEMA`	`STRING`	The name of this model's schema. Always an empty string.
`MODEL_NAME`	`STRING`	The name of the model.
`COLUMN_KIND`	`STRING`	Model column kind. One of: `"INPUT"` or `"OUTPUT"`.
`COLUMN_NAME`	`STRING`	The name of the column.
`DATA_TYPE`	`STRING`	The column's standard SQL data type.
`ORDINAL_POSITION`	`INT64`	Ordinal position of the column, starting with value of 1, to preserve the order of declared columns.
`IS_EXPLICIT`	`BOOL`	`TRUE` if the column was specified explicitly in the DDL, `FALSE` if the column was discovered from the endpoint.

`MODEL_COLUMN_OPTIONS`

This table contains the configuration options for model columns.

Column name	Type	Description
`MODEL_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`MODEL_SCHEMA`	`STRING`	The name of this model's schema. Always an empty string.
`MODEL_NAME`	`STRING`	The name of the model.
`COLUMN_KIND`	`STRING`	Model column kind. One of: `"INPUT"` or `"OUTPUT"`.
`COLUMN_NAME`	`STRING`	The name of the column.
`OPTION_NAME`	`STRING`	The name of the model column option.
`OPTION_TYPE`	`STRING`	The data type of the model column option.
`OPTION_VALUE`	`STRING`	The model column option value.

`MODEL_PRIVILEGES`

This row-filtered table lists all the privileges granted at the model-level to database roles, including public. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can see privileges only for models on which EXECUTE fine-grained access control privilege is granted to the current database role, to roles of which the current database role is a member, or to public.

Column name	Type	Description
`MODEL_CATALOG`	`STRING`	Not used. Always an empty string.
`MODEL_SCHEMA`	`STRING`	Not used. Always an empty string.
`MODEL_NAME`	`STRING`	The name of the model on which fine-grained access control privileges are granted.
`PRIVILEGE_TYPE`	`STRING`	`EXECUTE`>
`GRANTEE`	`STRING`	The name of the database role to which this privilege is granted.

`PROPERTY_GRAPHS`

This row-filtered table lists the property graphs in the database. Principals with database-level IAM permissions and principals who have been granted access to the spanner_info_reader system role or to members of that role can see all rows in this view. All other principals can only see property graphs if they meet the requirements to see all the tables used to define those graphs.

Column name	Type	Description
`PROPERTY_GRAPH_CATALOG`	`STRING`	The name of the catalog. Always an empty string.
`PROPERTY_GRAPH_SCHEMA`	`STRING`	The name of the schema. An empty string if unnamed.
`PROPERTY_GRAPH_NAME`	`STRING`	The name of the property graph.
`PROPERTY_GRAPH_METADATA_JSON`	`JSON`	The definition of the property graph in JSON format.

The PROPERTY_GRAPH_METADATA_JSON column contains a PropertyGraph JSON object defined as the following:

JSON object name	Field name	JSON type	Description
`PropertyGraph`	`catalog`	`string`	The name of the catalog. Always an empty string.
	`schema`	`string`	The name of the schema. An empty string if unnamed.
	`name`	`string`	The name of the property graph.
	`nodeTables`	`array<object>`	A list of `GraphElementTable` objects for nodes.
	`edgeTables`	`array<object>`	A list of `GraphElementTable` objects for edges.
	`labels`	`array<object>`	A list of `GraphElementLabel` objects.
	`propertyDeclarations`	`array<object>`	A list of `GraphPropertyDeclaration` objects.
`GraphElementTable`	`name`	`string`	The name of the graph element table.
	`kind`	`string`	Either `NODE` or `EDGE`.
	`baseCatalogName`	`string`	The name of the catalog containing the base table.
	`baseSchemaName`	`string`	The name of the schema containing the base table.
	`baseTableName`	`string`	The name of the input table from which elements are created.
	`keyColumns`	`array<string>`	The column names that constitute the element key.
	`labelNames`	`array<string>`	The label names attached to this element table.
	`propertyDefinitions`	`array<object>`	A list of `GraphPropertyDefinition` objects.
	`sourceNodeTable`	`object`	A `GraphNodeTableReference` object. Only exist when the `kind` is `EDGE`.
	`destinationNodeTable`	`object`	A `GraphNodeTableReference` object. Only exist when the `kind` is `EDGE`.
`GraphNodeTableReference`	`nodeTableName`	`string`	The name of the graph element table.
	`edgeTableColumns`	`array<string>`	The name of the columns that are associated with the source and destination keys for the edges.
	`nodeTableColumns`	`array<string>`	The name of the columns that are associated with the source and destination keys for the nodes.
`GraphElementLabel`	`name`	`string`	The name of the label.
`GraphElementLabel`	`propertyDeclarationNames`	`array<string>`	The names of the properties associated with this label.
`GraphPropertyDeclaration`	`name`	`string`	The name of the property.
`GraphPropertyDeclaration`	`type`	`string`	The type of the property.
`GraphPropertyDefinition`	`propertyDeclarationName`	`string`	The name of the property.
`GraphPropertyDefinition`	`valueExpressionSql`	`string`	The expression that defines the property.

Examples

Return information about each table in the user's schema:

SELECT
  t.table_name,
  t.parent_table_name
FROM
  information_schema.tables AS t
WHERE
  t.table_catalog = ''
  AND
  t.table_schema = ''
ORDER BY
  t.table_catalog,
  t.table_schema,
  t.table_name

Return the name of all tables in the INFORMATION_SCHEMA:

SELECT
  t.table_name
FROM
  information_schema.tables AS t
WHERE
  t.table_schema = "SPANNER_SYS"

Return information about the columns in the user table MyTable:

SELECT
  t.column_name,
  t.spanner_type,
  t.is_nullable
FROM
  information_schema.columns AS t
WHERE
  t.table_catalog = ''
  AND
  t.table_schema = ''
  AND
  t.table_name = 'MyTable'
ORDER BY
  t.table_catalog,
  t.table_schema,
  t.table_name,
  t.ordinal_position

Return information on what the default leader region for the database is. Returns empty if the default leader is not set:

SELECT
  s.option_name,
  s.option_value
FROM
  information_schema.database_options s
WHERE
  s.option_name = 'default_leader'

Return information about each index in the user's schema:

SELECT
  t.table_name,
  t.index_name,
  t.parent_table_name
FROM
  information_schema.indexes AS t
WHERE
  t.table_catalog = ''
  AND
  t.table_schema = ''
  AND
  t.index_type != 'PRIMARY_KEY'
ORDER BY
  t.table_catalog,
  t.table_schema,
  t.table_name,
  t.index_name

Returns all the columns that use options other than the default:

SELECT
  t.table_name,
  t.column_name,
  t.option_type,
  t.option_value,
  t.option_name
FROM
  information_schema.column_options AS t
WHERE
  t.table_catalog = ''
AND
  t.table_schema = ''

Returns the current optimizer related database options:

SELECT
  s.option_name,
  s.option_value
FROM
  information_schema.database_options s
WHERE
  s.schema_name=''
  AND s.option_name IN ('optimizer_version',
    'optimizer_statistics_package')

Returns all available statistics packages:

SELECT
  *
FROM
  information_schema.spanner_statistics;

Return all sequences:

SELECT
  *
FROM
  information_schema.sequences;

Return all sequence options for the sequence named "MySequence"

SELECT
  *
FROM
  information_schema.sequence_options WHERE name="MySequence";

Return the names of all property graphs and their definitions:

SELECT
  property_graph_name,
  property_graph_metadata_json
FROM
  information_schema.property_graphs

Return the names of all property graphs together with their labels and properties:

SELECT
  property_graph_name,
  property_graph_metadata_json.labels,
  property_graph_metadata_json.propertyDeclarations
FROM
  information_schema.property_graphs

What's next

Learn about available Introspection tools to help you investigate database issues.