Information schema for Google Standard SQL-dialect databases

Stay organized with collections Save and categorize content based on your preferences.

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_schema = ''

Usage

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

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

    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.

  • 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.

Tables in the INFORMATION_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.

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.

TABLES

This table lists the tables and views 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 or view.
TABLE_TYPE STRING The type of the table. For tables it has the value BASE TABLE; for views it has the value VIEW.
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.

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 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.
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 table lists all the privileges granted at the column-level to database roles.

Column name Type Description
TABLE_CATALOG STRING The name of the privileged column's table catalog. Always an empty string.
TABLE_SCHEMA STRING The name of the privileged column's table schema. 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 table lists all the privileges granted at the table-level to database roles.

Column name Type Description
TABLE_CATALOG STRING The name of the privileged table's catalog. Always an empty string.
TABLE_SCHEMA STRING The name of the privileged table's schema. Always an empty string.
TABLE_NAME STRING The name of the privileged table.
PRIVILEGE_TYPE STRING SELECT, INSERT, UPDATE, 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
  • 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 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.

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 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 KEYs, 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.

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

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.

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.

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 table lists information about the views 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 view.
VIEW_DEFINITION STRING The SQL text of the query that defines the view.

ROLES

This table lists information about database roles for fine-grained access control. Database roles are collections of privileges.

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 table lists information about database role grantees in a schema. role_name is a database role for fine-grained access control, and grantee is the role that has membership in role_name.

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 table lists all of a database's change streams, and notes which ones track the entire database versus specific tables or columns.

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 table contains the relationships between a database's tables and its change streams. Each row regards one database table and one change stream.

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 table contains the relationship between a database's columns and its change streams. Each row regards one change stream and one database column.

The data in CHANGE_STREAM_COLUMNS does not include the implicit relationships between columns and change streams that track those columns' entire tables.

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 table contains the configuration options for change streams.

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.

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;

What's next