Information schema for PostgreSQL-dialect databases

The information schema is a built-in schema that's common to every PostgreSQL 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 = 'public'

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 don't support information_schema.

Differences from information_schema for PostgreSQL

The tables in the information_schema for PostgreSQL-dialect databases include columns from the tables in the information_schema for open source PostgreSQL and in some cases also include columns from Spanner. In these tables, the open source PostgreSQL columns come first and in the same order as they do for a open source PostgreSQL database, and any distinct columns for Spanner are appended afterwards. Queries written for the open source PostgreSQL version of information_schema should work without modification when using PostgreSQL-dialect databases in Google Cloud CLI.

Other notable differences in the information_schema for PostgreSQL-dialect databases are:

  • Some of the table columns for open source PostgreSQL are available, but not populated in PostgreSQL-dialect databases.
  • PostgreSQL-dialect databases use public for the default schema name.
  • Automatically generated constraint names use a different format than open source PostgreSQL databases.
  • Tables related to open source PostgreSQL features that are not supported in PostgreSQL-dialect databases are not available.
  • Some tables that are available with Spanner but not open source PostgreSQL, such as database_options, index_columns, indexes, and spanner_statistics are available.

Row filtering in information_schema tables and views

Principals that have 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 and views. For other principals, Spanner filters rows based on the current database role. The table and view descriptions in the following sections indicate how Spanner filters rows for each table and view.

Tables in information_schema for PostgreSQL-dialect databases

The tables and views in the information_schema are compatible with the tables and views in the information_schema of open source PostgreSQL.

The following sections describe the tables and views in the information_schema for PostgreSQL-dialect databases.

applicable_roles

This row-filtered view lists all role memberships that are explicitly granted to all database roles. Principals that have 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 that are 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
grantee character varying The name of the database role to which membership is granted.
role_name character varying The name of the parent database role in which this membership is granted.
is_grantable character varying Not used. Always NO.

change_stream_columns

This row-filtered view 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 that have 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 character varying The database name.
change_stream_schema character varying The name of the change stream's schema. For PostgreSQL-dialect databases, the default is public.
change_stream_name character varying The name of the change stream.
table_catalog character varying The database name.
table_schema character varying The name of the table's schema. For PostgreSQL-dialect databases, the default is public.
table_name character varying The name of the table that this row refers to.
column_name character varying The name of the column that this row refers to.

change_stream_options

This row-filtered view contains the configuration options for change streams. Principals that have 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 character varying The database name.
change_stream_schema character varying The name of the change stream's schema. For PostgreSQL-dialect databases, the default is public.
change_stream_name character varying The name of the change stream.
option_name character varying The name of the change stream option.
option_type character varying The data type of the change stream option.
option_value character varying The value of the change stream option.

change_stream_privileges

This row-filtered view lists all fine-grained access control privileges granted on all change streams to any database role, including public. Principals that have 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
grantor character varying Not used. Always NULL.
grantee character varying The name of the database role to which this privilege is granted.
change_stream_catalog character varying The database name.
change_stream_schema character varying The name of the schema that contains the change stream. The default is public for PostgreSQL-dialect databases.
change_stream_name character varying The name of the change stream.
privilege_type character varying The type of the privilege (SELECT only).
is_grantable character varying Not used. Always NO.

change_stream_tables

This row-filtered view contains information about tables and the change streams that watch them. Each row describes one table and one change stream. Principals that have 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 character varying The database name.
change_stream_schema character varying The name of the change stream's schema. For PostgreSQL-dialect databases, the default is public.
change_stream_name character varying The name of the change stream that this row refers to.
table_catalog character varying The database name.
table_schema character varying The name of the table's schema. For PostgreSQL-dialect databases, the default is public.
table_name character varying The name of the table that this row refers to.
all_columns character varying YES if this row's change stream tracks the entirety of the table this row refers to. Otherwise, NO. In accordance with the SQL standard, the string is either YES or NO, rather than a Boolean value.

change_streams

This row-filtered view lists all of a database's change streams, and notes which ones track the entire database versus specific tables or columns. Principals that have 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 character varying The database name.
change_stream_schema character varying The name of this change stream's schema. For PostgreSQL-dialect databases, the default is public.
change_stream_name character varying The name of the change stream.
all character varying YES if this change stream tracks the entire database. NO if this change stream tracks specific tables or columns. In accordance with the SQL standard, the string is either YES or NO, rather than a Boolean value.

check_constraints

The check_constraints view contains one row for each check constraint defined by either the CHECK or the NOT NULL keyword.

Column name Type Description
constraint_catalog character varying The database name.
constraint_schema character varying The name of the constraint's schema. The default is public for PostgreSQL-dialect databases.
constraint_name character varying The name of the constraint. If the name of the constraint is not explicitly specified in the schema, the auto-generated name is used.
check_clause character varying The check constraint's expression.
spanner_state character varying The current state of the check constraint. The possible states are as follows:
  • VALIDATING: The PostgreSQL-dialect database is validating the existing data for an ALTER CONSTRAINT or ADD CONSTRAINT command.
  • COMMITTED: There is no active schema change for this constraint.

column_column_usage

This view lists all the generated columns that depend on another base column in the same table.

Column name Type Description
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the table. The name is public for the default schema and non-empty for other schemas (for example, the information_schema itself). This column is never null.
table_name character varying The name of the table that contains the generated columns.
column_name character varying The name of the base column that the generated column depends on.
dependent_column character varying The name of the generated column.

column_options

This view lists all the options defined for the referenced table columns of a foreign key constraint. The view contains only those columns in the reference table that the current user has access to (by way of being the owner or granted privileges).

Column name Type Description
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the foreign table. The name is public for the default schema and non-empty for other schemas (for example, the information_schema itself). This column is never null.
table_name character varying The name of the foreign table.
column_name character varying The name of the column.
option_name character varying A SQL identifier that uniquely identifies the option. This identifier is the key of the OPTIONS clause in DDL.
option_value character varying A SQL literal describing the value of this option. The value of this column is parsable as part of a query.
option_type character varying A data type name that is the type of this option value.

column_privileges

This row-filtered view lists all fine-grained access control privileges granted on all columns to any database role, including public. Principals that have 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 columns to the current database role, to roles of which the current database role is a member, or to 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 character varying Not used. Always NULL.
grantee character varying The name of the database role to which this privilege is granted.
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the table or view. The default is public for PostgreSQL-dialect databases.
table_name character varying The name of the table or view that contains the column.
column_name character varying The name of the column.
privilege_type character varying The type of the privilege (SELECT, INSERT, or UPDATE).
is_grantable character varying Not used. Always NO.

columns

This row-filtered view provides information about all table columns and view columns in the database. Principals that have 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 have any fine-grained access control privileges granted on them (or the SELECT, INSERT or UPDATE privileges granted on their containing tables) 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 character varying The database name.
table_schema character varying The name of the schema that contains the table. The name is public for the default schema and non-empty for other schemas (for example, the information_schema itself). This column is never null.
table_name character varying The name of the table
column_name character varying The name of the column
ordinal_position BIGINT The ordinal position of the column in the table, starting with a value of 1
column_default character varying A string representation of the open source PostgreSQL expression of the default value of the column, for example, '9'::bigint.
is_nullable character varying 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.
data_type character varying The data type of the column. The value is one of the following:
  • For built-in types, the name of the data type.
  • For arrays, the value ARRAY.
character_maximum_length BIGINT The declared maximum length for character and bit string data types. If a maximum length was not specified, then the value is NULL. If the data type of the column is not a character or bit string, then the value is NULL.
character_octet_length BIGINT Not used. The value is always NULL.
numeric_precision BIGINT The precision of the numeric data type of the current column. For double precision, the value is 53. For bigint, the value is 64. For all other data types, the value is NULL.
numeric_precision_radix BIGINT The base (unit) of the precision for numeric types. Only two values are supported:
  • 2 for double precision float8, and bigint
  • 10 for numeric
For all other data types the value is NULL.
numeric_scale BIGINT Contains the scale of the numeric column type, which is the number of precision base units after the radix point. For bigint, the value is 0. For all other data types, the value is NULL.
datetime_precision BIGINT Not used. The value is always NULL.
interval_type character varying Not used. The value is always NULL.
interval_precision BIGINT Not used. The value is always NULL.
character_set_catalog character varying Not used. The value is always NULL.
character_set_schema character varying Not used. The value is always NULL.
character_set_name character varying Not used. The value is always NULL.
collation_catalog character varying Not used. The value is always NULL.
collation_schema character varying Not used. The value is always NULL.
collation_name character varying Not used. The value is always NULL.
domain_catalog character varying Not used. The value is always NULL.
domain_schema character varying Not used. The value is always NULL.
domain_name character varying Not used. The value is always NULL.
udt_catalog character varying Not used. The value is always NULL.
udt_schema character varying Not used. The value is always NULL.
udt_name character varying Not used. The value is always NULL.
scope_catalog character varying Not used. The value is always NULL.
scope_schema character varying Not used. The value is always NULL.
scope_name character varying Not used. The value is always NULL.
maximum_cardinality BIGINT Not used. The value is always NULL.
dtd_identifier character varying Not used. The value is always NULL.
is_self_referencing character varying Not used. The value is always NULL.
is_identity character varying Not used. The value is always NULL.
identity_generation character varying Not used. The value is always NULL.
identity_start character varying Not used. The value is always NULL.
identity_increment character varying Not used. The value is always NULL.
identity_maximum character varying Not used. The value is always NULL.
identity_minimum character varying Not used. The value is always NULL.
identity_cycle character varying Not used. The value is always NULL.
is_generated character varying 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 character varying A string representing the SQL expression of a generated column, or NULL if the column is not a generated column.
is_updatable character varying Not used. The value is always NULL.
spanner_type character varying A string holding the DDL-compatible type of the column.
is_stored character varying A string that indicates whether the generated column is stored. The string is always YES or NO for generated columns, and NULL for non-generated columns.
spanner_state character varying 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:
  • NO_WRITE: No read or write is allowed to the columns. A stored generated column in this state does not cause any client effect.
  • WRITE_ONLY: The column is being backfilled. No read is allowed.
  • COMMITTED: The column is fully usable.
  • NULL: Used for columns in system schemas.

constraint_column_usage

This view contains one row about each column used by a constraint.

  • For PRIMARY KEY and CHECK constraints defined by the NOT NULL keyword, the view contains those columns.
  • For CHECK constraints created with the CHECK keyword, the view includes the columns used by the check constraint expression.
  • For foreign key constraints, the view contains the columns of the referenced table.
  • For UNIQUE constraints, the view contains the columns from KEY_COLUMN_USAGE.

Column name Type Description
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the table that contains the column that is used by the constraint.
table_name character varying The name of the table that contains the column that is used by the constraint.
column_name character varying The name of the column that is used by the constraint.
constraint_catalog character varying The database name.
constraint_schema character varying The name of the constraint's schema.
constraint_name character varying The name of the constraint.

constraint_table_usage

This view contains one row for each table used by a constraint. For FOREIGN KEY constraints, the table information is for the tables in the REFERENCES clause. For a unique or primary key constraint, this view identifies the table the constraint belongs to. Check constraints and not-null constraints are not included in this view.

Column name Type Description
table_catalog character varying The database name.
table_schema character varying The name of the constrained table's schema.
table_name character varying The name of the table that is used by some constraint.
constraint_catalog character varying The database name.
constraint_schema character varying The name of the schema that contains the constraint.
constraint_name character varying The name of the constraint.

database_options

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

Column name Type Description
catalog_name character varying The database name.
schema_name character varying The name of the schema. The default value is public for PostgreSQL-dialect databases.
option_name character varying The name of the database option. This is the value of key in the OPTIONS clause in DDL.
option_type character varying The data type of the database option.
option_value character varying The value of the database option.

enabled_roles

This row-filtered view lists the defined database roles. Principals that have 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. All system roles excluding public also appear in this view.

Column name Type Description
role_name character varying The name of the role.
spanner_is_system character varying YES if the role is a system role. Otherwise, NO.

index_columns

This view lists the columns in an index.

Column name Type Description
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the index. The default value is public.
table_name character varying The name of the table associated with the index.
index_name character varying The name of the index. Tables that have a PRIMARY KEY specification have a pseudo-index entry generated with the name PRIMARY_KEY.
index_type character varying The type of index. Possible values are PRIMARY_KEY, LOCAL, or GLOBAL.
column_name character varying The name of the column.
ordinal_position BIGINT 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 INCLUDE clause of an index).
column_ordering character varying The sort order 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 character varying 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 character varying A string holding the DDL-compatible type of the column.

indexes

This view lists the indexes in a schema.

Column name Type Description
table_catalog character varying The database name.
table_schema character varying The name of the schema. The default value is public.
table_name character varying The name of the table.
index_name character varying The name of the index. Tables created with a PRIMARY KEY clause have a pseudo-index entry generated with the name PRIMARY_KEY, which allows the fields of the primary key to be identified.
index_type character varying The type of the index. The values include PRIMARY_KEY, LOCAL, or GLOBAL.
parent_table_name character varying 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 character varying Whether the index keys must be unique. In accordance with the SQL standard, the string is either YES or NO, rather than a Boolean value.
is_null_filtered character varying Whether the index includes entries with NULL values. In accordance with the SQL standard, the string is either YES or NO, rather than a Boolean value.
index_state character varying The current state of the index. Possible values and the states they represent are:
  • NULL: the index type is PRIMARY_KEY
  • 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 character varying Whether the index is managed by Spanner. For example, secondary backing indexes for foreign keys are managed by Spanner. The string is either YES or NO, rather than a Boolean value, in accordance with the SQL standard.

information_schema_catalog_name

This table contains one row and one column containing the database name.

Column name Type Description
catalog_name character varying The database name.

key_column_usage

This view identifies all columns in the current database that are referenced by a unique, primary key, or foreign key constraint. For information about CHECK constraint columns, see the check_constraints view.

Column name Type Description
constraint_catalog character varying The database name.
constraint_schema character varying The name of the constraint's schema. The default value is public.
constraint_name character varying The name of the constraint.
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the table that contains the constrained column. The default value is public.
table_name character varying The name of the table that contains the column that is restricted by this constraint.
column_name character varying The name of the column that is constrained.
ordinal_position BIGINT The ordinal position of the column within the constraint's key, starting with a value of 1.
position_in_unique_constraint BIGINT For FOREIGN KEYs, the ordinal position of the column within the unique constraint, starting with a value of 1. This column has a NULL value for other constraint types.

parameters

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

Principals that have 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 character varying The database name.
specific_schema character varying The name of the routine's schema. For PostgreSQL-dialect databases, the default is public.
specific_name character varying The name of the routine. Uniquely identifies the routine even if its name is overloaded.
ordinal_position bigint The ordinal position of the parameter in the argument list of the routine, starting with a value 1.
parameter_mode character varying Not used. Always NULL.
is_result character varying Not used. Always NULL.
as_locator character varying Not used. Always NULL.
parameter_name character varying The name of the parameter.
data_type character varying The data type of the parameter. The value is one of the following:
  • For built-in types, the name of the data type.
  • For arrays, the value ARRAY.
character_maximum_length bigint Not used. Always NULL.
character_octet_length bigint Not used. The value is always NULL.
character_set_catalog character varying Not used. The value is always NULL.
character_set_schema character varying Not used. The value is always NULL.
character_set_name character varying Not used. The value is always NULL.
collation_catalog character varying Not used. The value is always NULL.
collation_schema character varying Not used. The value is always NULL.
collation_name character varying Not used. The value is always NULL.
numeric_precision bigint Not used. The value is always NULL.
numeric_precision_radix bigint Not used. The value is always NULL.
numeric_scale bigint Not used. The value is always NULL.
datetime_precision bigint Not used. The value is always NULL.
interval_type character varying Not used. The value is always NULL.
interval_precision bigint Not used. The value is always NULL.
udt_catalog character varying Not used. The value is always NULL.
udt_schema character varying Not used. The value is always NULL.
udt_name character varying Not used. The value is always NULL.
scope_catalog character varying Not used. The value is always NULL.
scope_schema character varying Not used. The value is always NULL.
scope_name character varying Not used. The value is always NULL.
maximum_cardinality bigint Not used. The value is always NULL.
dtd_identifier character varying Not used. The value is always NULL.
parameter_default character varying Not used. The value is always NULL.

placements

This table lists the placements in the database.

Column name Type Description
placement_name character varying The name of the placement.
is_default character varying 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.

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.

Column name Type Description
placement_name character varying The name of the placement.
option_name character varying The name of the placement option. The valid values for option_name include:
  • instance_partition
  • default_leader
option_type character varying The data type of the placement option.
option_value character varying The value of the placement option. For instance_partition, this is the name of the instance partition. For default_leader, it's the name of the default leader region.

referential_constraints

This view contains one row about each FOREIGN KEY constraint. You can see only those constraints for which you have write access to the referencing table. This view also identifies the PRIMARY KEY and UNIQUE constraints on the referenced tables that the foreign keys use for constraint enforcement and referential actions.

Column name Type Description
constraint_catalog character varying The database name.
constraint_schema character varying The name of the schema that contains the foreign key constraint. The default value is public.
constraint_name character varying The name of the foreign key constraint.
unique_constraint_catalog character varying The database name.
unique_constraint_schema character varying The name of the schema that contains the unique or primary key constraint that the foreign key constraint references.
unique_constraint_name character varying The name of the unique or primary key constraint that the foreign key constraint references.
match_option character varying The match method used by the foreign key constraint. The value is always NONE.
update_rule character varying The update rule of the foreign key constraint. This value is always NO ACTION.
delete_rule character varying The delete rule of the foreign key constraint. This value is either CASCADE or NO ACTION.
spanner_state character varying 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. The 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.

role_change_stream_grants

This row-filtered view lists the SELECT privileges granted on all change streams to any database role, including public. Principals that have 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
grantor character varying Not used. Always NULL.
grantee character varying The name of the database role to which this privilege is granted.
change_stream_catalog character varying The database name.
change_stream_schema character varying The name of the schema that contains the change stream. The default is public for PostgreSQL-dialect databases.
change_stream_name character varying The name of the change stream.
privilege_type character varying The type of the privilege (SELECT only).
is_grantable character varying Not used. Always NO.

role_column_grants

This row-filtered view lists all fine-grained access control privileges granted on all columns to any database role, including public. Principals that have 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 character varying Not used. Always NULL.
grantee character varying The name of the database role to which this privilege is granted.
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the table or view. The default is public for PostgreSQL-dialect databases.
table_name character varying The name of the table or view that contains the column.
column_name character varying The name of the column.
privilege_type character varying The type of the privilege (SELECT, INSERT, or UPDATE).
is_grantable character varying Not used. Always NO.

role_routine_grants

This row-filtered view lists the EXECUTE privileges granted on all change stream read functions to any database role, including public. Principals that have 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 and to roles of which the current database role is a member, not including public.

Column name Type Description
grantor character varying Not used. Always NULL.
grantee character varying The name of the role that the privilege was granted to.
specific_catalog character varying The database name.
specific_schema character varying The name of the routine's schema. For PostgreSQL-dialect databases, the default is public.
specific_name character varying The name of the routine. Uniquely identifies the routine even if its name is overloaded.
routine_catalog character varying The database name.
routine_schema character varying The name of the routine's schema. The default is public for PostgreSQL-dialect databases.
routine_name character varying The name of the routine. (Might be duplicated in case of overloading.)
privilege_type character varying The type of the privilege granted. Always EXECUTE.
is_grantable character varying Not used. Always NO.

role_table_grants

This row-filtered view lists all fine-grained access control privileges granted on all tables and views to any database role, including public. Principals that have 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 character varying Not used. Always NULL.
grantee character varying The name of the database role to which this privilege is granted.
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the table or view. The default is public for PostgreSQL-dialect databases.
table_name character varying The name of the table or view.
privilege_type character varying The type of the privilege (SELECT, INSERT, UPDATE, or DELETE).
is_grantable character varying Not used. Always NO.
with_hierarchy character varying Not used. Always NULL.

routine_options

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

Principals that have 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 character varying The database name.
specific_schema character varying The name of the routine's schema. The default is public for PostgreSQL-dialect databases.
specific_name character varying The name of the routine. Uniquely identifies the routine even if its name is overloaded.
option_name character varying The name of the option.
option_type character varying The data type of the option. The value is one of the following:
  • For built-in types, the name of the data type.
  • For arrays, the value ARRAY.
option_value character varying The value of the option.

routine_privileges

This row-filtered view lists all fine-grained access control privileges granted on all change stream read functions to any database role, including public. Principals that have 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
grantor character varying Not used. Always NULL.
grantee character varying The name of the role that the privilege was granted to.
specific_catalog character varying The database name.
specific_schema character varying The name of the routine's schema. For PostgreSQL-dialect databases, the default is public.
specific_name character varying The name of the routine. Uniquely identifies the routine even if its name is overloaded.
routine_catalog character varying The database name.
routine_schema character varying The name of the routine's schema. The default is public.
routine_name character varying The name of the routine. (Might be duplicated if overloaded.)
privilege_type character varying The type of the privilege granted.
is_grantable character varying Not used. Always NO.

routines

This row-filtered view lists all of a database's change stream read functions. Principals that have 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 character varying The database name.
specific_schema character varying The name of the routine's schema. The default is public for PostgreSQL-dialect databases.
specific_name character varying The name of the routine. Uniquely identifies the routine even if its name is overloaded.
routine_catalog character varying The database name.
routine_schema character varying The name of the routine's schema.
routine_name character varying The name of the routine. (Might be duplicated in case of overloading.)
routine_type character varying The type of the routine (FUNCTION or PROCEDURE). Always FUNCTION
module_catalog character varying Not used. The value is always NULL.
module_schema character varying Not used. The value is always NULL.
module_name character varying Not used. The value is always NULL.
udt_catalog character varying Not used. The value is always NULL.
udt_schema character varying Not used. The value is always NULL.
udt_name character varying Not used. The value is always NULL.
data_type character varying The return type of the routine. The value is one of the following:
  • For built-in types, the name of the data type.
  • For arrays, the value ARRAY.
character_maximum_length bigint Not used. The value is always NULL.
character_octet_length bigint Not used. The value is always NULL.
character_set_catalog character varying Not used. The value is always NULL.
character_set_schema character varying Not used. The value is always NULL.
character_set_name character varying Not used. The value is always NULL.
collation_catalog character varying Not used. The value is always NULL.
collation_schema character varying Not used. The value is always NULL.
collation_name character varying Not used. The value is always NULL.
numeric_precision bigint Not used. The value is always NULL.
numeric_precision_radix bigint Not used. The value is always NULL.
numeric_scale bigint Not used. The value is always NULL.
datetime_precision bigint Not used. The value is always NULL.
interval_type character varying Not used. The value is always NULL.
interval_precision bigint Not used. The value is always NULL.
type_udt_catalog character varying Not used. The value is always NULL.
type_udt_schema character varying Not used. The value is always NULL.
type_udt_name character varying Not used. The value is always NULL.
scope_catalog character varying Not used. The value is always NULL.
scope_schema character varying Not used. The value is always NULL.
scope_name character varying Not used. The value is always NULL.
maximum_cardinality bigint Not used. The value is always NULL.
dtd_identifier character varying Not used. The value is always NULL.
routine_body character varying The type of the routine body (SQL or EXTERNAL).
routine_definition character varying The definition for the routine_body SQL, empty otherwise.
external_name character varying Not used. The value is always NULL.
external_language character varying Not used. The value is always NULL.
parameter_style character varying Not used. The value is always NULL.
is_deterministic character varying Not used. The value is always NULL.
sql_data_access character varying Not used. The value is always NULL.
is_null_call character varying Not used. The value is always NULL.
sql_path character varying Not used. The value is always NULL.
schema_level_routine character varying Not used. The value is always NULL.
max_dynamic_result_sets character varying Not used. The value is always NULL.
is_user_defined_cast character varying Not used. The value is always NULL.
is_implicitly_invocable character varying Not used. The value is always NULL.
security_type character varying The security type of the routine. Only INVOKER is supported.
to_sql_specific_catalog character varying Not used. The value is always NULL.
to_sql_specific_schema character varying Not used. The value is always NULL.
to_sql_specific_name character varying Not used. The value is always NULL.
as_locator character varying Not used. The value is always NULL.
created timestamp with time zone Not used. The value is always NULL.
last_altered timestamp with time zone Not used. The value is always NULL.
new_savepoint_level character varying Not used. The value is always NULL.
is_udt_dependent character varying Not used. The value is always NULL.
result_cast_from_data_type character varying Not used. The value is always NULL.
result_cast_as_locator character varying Not used. The value is always NULL.
result_cast_char_max_length bigint Not used. The value is always NULL.
result_cast_char_octet_length bigint Not used. The value is always NULL.
result_cast_char_set_catalog character varying Not used. The value is always NULL.
result_cast_char_set_schema character varying Not used. The value is always NULL.
result_cast_char_set_name character varying Not used. The value is always NULL.
result_cast_collation_catalog character varying Not used. The value is always NULL.
result_cast_collation_schema character varying Not used. The value is always NULL.
result_cast_collation_name character varying Not used. The value is always NULL.
result_cast_numeric_precision bigint Not used. The value is always NULL.
result_cast_numeric_precision_radix bigint Not used. The value is always NULL.
result_cast_numeric_scale bigint Not used. The value is always NULL.
result_cast_datetime_precision bigint Not used. The value is always NULL.
result_cast_interval_type character varying Not used. The value is always NULL.
result_cast_interval_precision bigint Not used. The value is always NULL.
result_cast_type_udt_catalog character varying Not used. The value is always NULL.
result_cast_type_udt_schema character varying Not used. The value is always NULL.
result_cast_type_udt_name character varying Not used. The value is always NULL.
result_cast_scope_catalog character varying Not used. The value is always NULL.
result_cast_scope_schema character varying Not used. The value is always NULL.
result_cast_scope_name character varying Not used. The value is always NULL.
result_cast_maximum_cardinality bigint Not used. The value is always NULL.
result_cast_dtd_identifier character varying Not used. The value is always NULL.

schemata

The information_schema.schemata view contains one row for each schema in the current database. The schemas include the information schema and a default schema named public.

Column name Type Description
catalog_name character varying The database name.
schema_name character varying The name of the schema. This is set to public for the default schema and non-empty for named schemas.
schema_owner character varying The name of the owner of the schema.
default_character_set_catalog character varying Not used.
default_character_set_schema character varying Not used.
default_character_set_name character varying Not used.
sql_path character varying Not used.
effective_timestamp timestamp with timezone The timestamp at which all the data in this schema became effective. This is used only for the default schema.

sequences

The information_schema.sequences view contains the sequences metadata.

Column name Type Description
sequence_catalog character varying The database name.
sequence_schema character varying The name of the sequence's schema. The default is public for a PostgreSQL-dialect database.
sequence_name character varying The name of the sequence.
data_type character varying Sequence only supports int8.
numeric_precision bigint Not used. The value is always `NULL`.
numeric_precision_radix bigint Not used. The value is always `NULL`.
numeric_scale bigint Not used. The value is always `NULL`.
start_value bigint Not used. The value is always `NULL`.
minimum_value bigint Not used. The value is always `NULL`.
maximum_value bigint Not used. The value is always `NULL`.
increment bigint Not used. The value is always `NULL`.
cycle_option character varying The only option that sequence accepts is no.
sequence_kind character varying The kind of sequence. bit_reversed_positive is the only acceptable value.
counter_start_value bigint Starting value of the sequence counter.
skip_range_min bigint The minimum value in the skipped range. This value is NULL if not set.
skip_range_max bigint The maximum value in the skipped range. This value is NULL if not set.

spanner_statistics

This table lists the available query optimizer statistics packages.

Column name Type Description
catalog_name character varying The database name.
schema_name character varying The name of the schema. The default schema value is public.
package_name character varying The name of the statistics package.
allow_gc character varying Whether the statistics package is exempted from garbage collection. In accordance with the SQL standard, the string is either YES or NO, rather than a Boolean value. This attribute must be set to NO before you can reference the statistics package in a hint or through the client API.

table_constraints

This view contains all constraints belonging to tables that the current user has access to (other than SELECT).

Column name Type Description
constraint_catalog character varying The database name.
constraint_schema character varying The name of the schema that contains the constraint.
constraint_name character varying The name of the constraint.
table_catalog character varying The database name.
table_schema character varying The name of schema that contains the table associated with the constraint.
table_name character varying The name of the table.
constraint_type character varying The type of the constraint. Possible values are:
  • CHECK
  • FOREIGN KEY
  • PLACEMENT KEY
  • PRIMARY KEY
  • UNIQUE
is_deferrable character varying The value is always NO.
initially_deferred character varying The value is always NO.
enforced character varying Whether the constraint is enforced. If a constraint is enforced, (after it reaches a certain state), it's validated both at write time and by a background integrity verifier. In accordance with the SQL standard, the string is either YES or NO, rather than a Boolean value.

table_privileges

This row-filtered view lists all fine-grained access control privileges granted on all tables and views to any database role, including public. Principals that have 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, to roles of which the current database role is a member, or to public.

Column name Type Description
grantor character varying Not used. Always NULL.
grantee character varying The name of the database role to which this privilege is granted.
table_catalog character varying The database name.
table_schema character varying The name of the schema that contains the table or view. The default is public for PostgreSQL-dialect databases.
table_name character varying The name of the table or view.
privilege_type character varying The type of the privilege (SELECT, INSERT, UPDATE, or DELETE).
is_grantable character varying Not used. Always NO.
that have_hierarchy character varying Not used. Always NULL.

tables

This row-filtered view lists all the tables and view that are in the current database. Principals that have 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 tables and views. 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 character varying The database name.
table_schema character varying The name of the schema that contains the table or view.
table_name character varying The name of the table, view, or synonym.
table_type character varying The table type. Possible values include 'BASE TABLE', 'VIEW', or 'SYNONYM'.
self_referencing_column_name character varying Not used.
reference_generation character varying Not used.
user_defined_type_catalog character varying Not used.
user_defined_type_schema character varying Not used.
user_defined_type_name character varying Not used.
is_insertable_into character varying Not used.
is_typed character varying Not used.
commit_action character varying Not used.
parent_table_name character varying The name of the parent table if this table is interleaved, or NULL.
on_delete_action character varying This is set to CASCADE or NO ACTION for interleaved tables, and NULL otherwise. See TABLE statements for more information.
spanner_state character varying The current creation state of the table.
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 referenced index. 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.
  • NULL: Tables or views that are not base tables.
interleave_type character varying Whether there exists a parent-child relationship between this table and the table it is interleaved in. Possible values are:
  • IN: An INTERLEAVE IN table that has no parent-child relationship. A row in this table can exist regardless of the existence of its parent table row.
  • IN PARENT: An INTERLEAVE IN PARENT table that has a parent-child relationship. A row in this table requires the existence of its parent table row.
row_deletion_policy_expression character varying An string that contains the expression text that defines the ROW DELETION POLICY.

table_synonyms

This table lists synonym information for the table.

Column name Type Description
CATALOG STRING Name of the catalog containing the table.
SCHEMA STRING Name of the schema containing the table.
TABLE_NAME STRING 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.

views

This row-filtered view lists all views in the current database. Principals that have 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 views. All other principals can see only views that have the SELECT fine-grained access control privilege granted on them 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 character varying The database name.
table_schema character varying The name of the schema. The default value is public.
table_name character varying The name of the view.
view_definition character varying The SQL text of the query that defines the view.
check_option character varying Not used.
is_updatable character varying Not used.
is_insertable_into character varying Not used.
is_trigger_updatable character varying Not used.
is_trigger_deletable character varying Not used.
is_trigger_insertable_into character varying Not used.
security_type character varying The security type of the view. Either INVOKER or DEFINER.

For more information, see About views.

Examples

Return information about each table in the default schema:

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

Return the name of all tables and views in the information_schema for PostgreSQL-dialect databases:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = "information_schema"

Return information about the columns in the user table my_table:

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

Return information about each index in the default schema in the current database: ```sql SELECT t.table_name, t.index_name, t.parent_table_name FROM information_schema.indexes AS t WHERE t.table_schema = 'public' AND t.index_type != 'PRIMARY_KEY' ORDER BY t.table_schema, t.table_name, t.index_name

Return 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_schema = 'public'
ORDER BY
  t.table_schema,
  t.table_name,
  t.column_name,
  t.option_name

Return the current optimizer-related database options: 

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

Return all available statistics packages: 

SELECT *
FROM information_schema.spanner_statistics;

What's next