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_schema 
 , 
  
 table_name 
  
 FROM 
  
 information_schema 
 . 
 tables 
  
 WHERE 
  
 table_schema 
  
 NOT 
  
 IN 
  
 ( 
 'information_schema' 
 , 
  
 'SPANNER_SYS' 
 ) 
  
 AND 
  
 table_type 
  
 = 
  
 'BASE TABLE' 
 

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

DATABASE_OPTIONS

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

PLACEMENTS

This table lists the placements in the database.

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

LOCALITY_GROUP_OPTIONS

For each locality group, this table lists the name and options that are set on the locality group in the OPTIONS clause of the CREATE LOCALITY GROUP statement.

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 .

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

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 .

TABLE_CONSTRAINTS

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

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.

REFERENTIAL_CONSTRAINTS

This table contains one row about each FOREIGN KEY constraint.

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.

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 .

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.

TABLE_SYNONYMS

This table lists lists synonym information for the table.

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 .

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

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.

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.

SPANNER_STATISTICS

This table lists the available query optimizer statistics packages.

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 .

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.

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.

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 .

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.

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 .

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 .

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 .

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 .

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 .

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 .

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 .

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 .

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.

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 .

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 .

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 .

MODELS

This table lists all of a database's models .

MODEL_OPTIONS

This table contains the configuration options for models.

MODEL_COLUMNS

This table lists the columns in a model.

MODEL_COLUMN_OPTIONS

This table contains the configuration options for model columns.

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 .

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.

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

Examples

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

  SELECT 
  
 t 
 . 
 table_schema 
 , 
  
 t 
 . 
 table_name 
 , 
  
 t 
 . 
 parent_table_name 
 FROM 
  
 information_schema 
 . 
 tables 
  
 AS 
  
 t 
 WHERE 
  
 t 
 . 
 table_catalog 
  
 = 
  
 '' 
  
 AND 
  
 t 
 . 
 table_schema 
  
 NOT 
  
 IN 
  
 ( 
 'information_schema' 
 , 
  
 'SPANNER_SYS' 
 ) 
  
 AND 
  
 t 
 . 
 table_type 
  
 = 
  
 'BASE TABLE' 
 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 in default schema:

  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_schema 
 , 
  
 t 
 . 
 table_name 
 , 
  
 t 
 . 
 index_name 
 , 
  
 t 
 . 
 parent_table_name 
 FROM 
  
 information_schema 
 . 
 indexes 
  
 AS 
  
 t 
 WHERE 
  
 t 
 . 
 table_catalog 
  
 = 
  
 '' 
  
 AND 
  
 t 
 . 
 table_schema 
  
 NOT 
  
 IN 
  
 ( 
 'information_schema' 
 , 
  
 'SPANNER_SYS' 
 ) 
  
 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_schema 
 , 
  
 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 
  
 NOT 
  
 IN 
  
 ( 
 'information_schema' 
 , 
  
 'SPANNER_SYS' 
 ) 
 

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.