close
The Wayback Machine - https://web.archive.org/web/20190812095608/https://cloud.google.com/spanner/docs/information-schema

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.

Schemas

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.

Tables

The INFORMATION_SCHEMA.TABLES table lists the tables in a schema.

Column name Type Description
TABLE_CATALOG STRING The name of the catalog. This column is never null, but always an empty string.
TABLE_SCHEMA STRING The name of the schema. This column is never null. The default schema has an empty string, and named schemas have a non-empty string.
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.

Table columns

The INFORMATION_SCHEMA.COLUMNS table lists the columns in a table.

Column name Type Description
TABLE_CATALOG STRING The name of the catalog. This column is never null, but always an empty string.
TABLE_SCHEMA STRING The name of the schema. This column is never null. The default schema has an empty string, and named schemas have a non-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.
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.

Indexes

The INFORMATION_SCHEMA.INDEXES table lists the indexes in a schema.

Column name Type Description
TABLE_CATALOG STRING The name of the catalog. This column is never null, but always an empty string.
TABLE_SCHEMA STRING The name of the schema. This column is never null. The default schema has an empty string, and named schemas have a non-empty string.
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.

Index columns

The INFORMATION_SCHEMA.INDEX_COLUMNS table lists the columns in an index.

Column name Type Description
TABLE_CATALOG STRING The name of the catalog. This column is never null, but always an empty string.
TABLE_SCHEMA STRING The name of the schema. This column is never null. The default schema has an empty string, and named schemas have a non-empty string.
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

The INFORMATION_SCHEMA.COLUMN_OPTIONS table lists the column options in a table.

Column name Type Description
TABLE_CATALOG STRING The name of the catalog. The name is always an empty string. This column is never null.
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. This column is never null.
COLUMN_NAME STRING The name of the column. This column is never null.
OPTION_NAME STRING A SQL identifier that uniquely identifies the option. This identifier is the key of the OPTIONS clause in DDL. This column is never null.
OPTION_TYPE STRING A data type name that is the type of this option value. This column is never null.
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 = ''

What's next

Learn about other metadata Cloud Spanner stores for each database in the database's query statistics tables.

Was this page helpful? Let us know how we did:

Send feedback about...

This page
Documentation feedback
BERJAYA
Cloud Spanner Documentation
Product feedback