Information schema

The information schema is a built-in schema that's common to every Cloud 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_catalog = '' and table_schema = ''

Usage

INFORMATION_SCHEMA data is available only through SQL interfaces (for example, executeQuery and gcloud spanner databases execute-sql); Cloud Spanner's other single read methods do not support INFORMATION_SCHEMA.
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.

Tables in the schema

SCHEMATA

The INFORMATION_SCHEMA.SCHEMATA table lists the schemas in the database. These include the information schema and an unnamed schema (hereafter called the "default schema"), which contains 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 empty for the default schema and non-empty for named schemas.

`INFORMATION_SCHEMA.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.

`INFORMATION_SCHEMA.TABLES`

This table lists the tables in a schema.

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.
`PARENT_TABLE_NAME`	`STRING`	The name of the parent table if this table is interleaved, or `NULL`.
`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 cannot write to the table until the change is committed.

`INFORMATION_SCHEMA.COLUMNS`

This table lists the columns in a table.

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.
`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`	`BYTES`	Included to satisfy the SQL standard. Always `NULL`.
`DATA_TYPE`	`STRING`	Included to satisfy the SQL standard. Always `NULL`.
`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.

`INFORMATION_SCHEMA.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` `CHECK` `UNIQUE`
`IS_DEFERRABLE`	`STRING`	Always `NO`.
`INITIALLY_DEFERRED`	`STRING`	Always `NO`.
`ENFORCED`	`STRING`	Always `YES`.

`INFORMATION_SCHEMA.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.

`INFORMATION_SCHEMA.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`	Always `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.

`INFORMATION_SCHEMA.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`: Cloud Spanner is validating the existing data. `COMMITTED`: There is no active schema change for this constraint.

`INFORMATION_SCHEMA.KEY_COLUMN_USAGE`

This 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. These are the columns of the tables that define the constraints.

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.

`INFORMATION_SCHEMA.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.

`INFORMATION_SCHEMA.INDEXES`

This table lists the indexes in a schema.

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 `NULL` 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 Cloud Spanner; Otherwise, False. Secondary backing indexes for foreign keys are managed by Cloud Spanner.

`INFORMATION_SCHEMA.INDEX_COLUMNS`

This table lists the columns in an index.

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.

`INFORMATION_SCHEMA.COLUMN_OPTIONS`

This table lists the column options in a table.

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.

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 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 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 query optimizer version the database is currently using:

SELECT
  s.OPTION_NAME,
  s.OPTION_VALUE
FROM
  INFORMATION_SCHEMA.DATABASE_OPTIONS s
WHERE
  s.SCHEMA_NAME=''
  AND s.OPTION_NAME = 'optimizer_version'

What's next

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