information_schema

The information_schema contains information about your database’s tables, columns, indexes, and views. This information can be used for introspection and reflection.

Data exposed by information_schema

To perform introspection on objects, you can either read from the related information_schema table or use one of CockroachDB’s SHOW statements. information_schema tables are read-only.

Object	information\_schema Table	Corresponding SHOW Statement
Columns	columns
Constraints	check\_constraints, key\_column\_usage, referential\_constraints, table\_constraints
Databases	schemata
Indexes	statistics
Privileges	schema\_privileges, table\_privileges
Roles	role\_table\_grants
Sequences	sequences
Tables	tables
Views	tables, views

Tables in information_schema

The virtual schema information_schema contains virtual tables, also called “system views,” representing the database’s objects, each of which is detailed below.

These differ from regular in that they do not show data created from the content of other tables. Instead, CockroachDB generates the data for virtual tables when they are accessed.

A query can specify a table name without a database name (e.g., SELECT * FROM information_schema.sequences). See for more information.

The virtual tables in information_schema contain useful comments with links to further documentation. To view these comments, use SHOW TABLES FROM information_schema WITH COMMENT:

administrable_role_authorizations

administrable_role_authorizations identifies all roles that the current user has the admin option for.

Column	Description
grantee	Name of the user to which this role membership was granted (always the current user).
role\_name	Name of a role.
is\_grantable	YES if the grantee has the admin option on the role; NO if not.

applicable_roles

applicable_roles identifies all roles whose privileges the current user can use. This implies there is a chain of role grants from the current user to the role in question. The current user itself is also an applicable role, but is not listed.

Column	Description
grantee	Name of the user to which this role membership was granted (always the current user).
role\_name	Name of a role.
is\_grantable	YES if the grantee has the admin option on the role; NO if not.

character_sets

character_sets identifies the character sets available in the current database.

Column	Description
character\_set\_catalog	Always NULL (unsupported by CockroachDB).
character\_set\_schema	Always NULL (unsupported by CockroachDB).
character\_set\_name	Name of the character set (i.e., the name of the database encoding). Always UTF8.
character\_repertoire	Character repertoire. UCS if the encoding is UTF8; the encoding name if not.
form\_of\_use	Character encoding form (i.e., the name of the database encoding). Always UTF8.
default\_collate\_catalog	Name of the database containing the default collation (if any collation is identified, always the current database).
default\_collate\_schema	Always NULL (unsupported by CockroachDB).
default\_collate\_name	Always NULL (unsupported by CockroachDB).

check_constraints

check_constraints contains information about the constraints applied to columns in a database.

Column	Description
constraint\_catalog	Name of the database containing the constraint.
constraint\_schema	Name of the schema containing the constraint.
constraint\_name	Name of the constraint.
check\_clause	Definition of the CHECK constraint.

collations

collations identifies the collations available in the current database.

Column	Description
collation\_catalog	Name of the database containing the collation (always the current database).
collation\_schema	Name of the schema containing the collation (always pg\_catalog).
collation\_name	Name of the collation.
pad\_attribute	Always NO PAD (PAD SPACE is not supported by CockroachDB).

collation_character_set_applicability

collation_character_set_applicability identifies which character set the available collations are applicable to.

Column	Description
collation\_catalog	Name of the database containing the collation (always the current database).
collation\_schema	Name of the schema containing the collation (always pg\_catalog).
collation\_name	Name of the collation.
character\_set\_catalog	Always NULL (unsupported by CockroachDB).
character\_set\_schema	Always NULL (unsupported by CockroachDB).
character\_set\_name	Name of the character set (always UTF8).

columns

columns contains information about the columns in each table.

Column	Description
table\_catalog	Name of the database containing the table.
table\_schema	Name of the schema containing the table.
table\_name	Name of the table.
column\_name	Name of the column.
column\_comment	Comment on the column.
ordinal\_position	Ordinal position of the column in the table (begins at 1).
column\_default	Default value for the column.
is\_nullable	YES if the column accepts NULL values; NO if it doesn’t (e.g., it has the ).
data\_type	of the column.
character\_maximum\_length	If data\_type is STRING, the maximum length in characters of a value; otherwise NULL.
character\_octet\_length	If data\_type is STRING, the maximum length in octets (bytes) of a value; otherwise NULL.
numeric\_precision	If data\_type is numeric, the declared or implicit precision (i.e., number of significant digits); otherwise NULL.
numeric\_precision\_radix	If data\_type identifies a numeric type, the base in which the values in the columns numeric\_precision and numeric\_scale are expressed (either 2 or 10). For all other data types, column is NULL.
numeric\_scale	If data\_type is an exact numeric type, the scale (i.e., number of digits to the right of the decimal point); otherwise NULL.
datetime\_precision	The precision level of columns with data type , , or . For all other data types, this column is NULL.
interval\_type	If data\_type is , the specified fields (e.g., YEAR TO MONTH); otherwise NULL.
interval\_precision	If data\_type is , the declared or implicit precision (i.e., number of significant digits); otherwise NULL.
character\_set\_catalog	Always NULL (unsupported by CockroachDB).
character\_set\_schema	Always NULL (unsupported by CockroachDB).
character\_set\_name	Always NULL (unsupported by CockroachDB).
collation\_catalog	Name of the database containing the collation (always the current database); NULL if the default collation is used, or if data\_type is not collatable.
collation\_schema	Name of the schema containing the collation; NULL if the default collation is used, or if data\_type is not collatable.
collation\_name	Name of the collation; NULL if the default collation is used, or if data\_type is not collatable.
domain\_catalog	Always NULL (unsupported by CockroachDB).
domain\_schema	Always NULL (unsupported by CockroachDB).
domain\_name	Always NULL (unsupported by CockroachDB).
udt\_catalog	Name of the column data type’s database (always the current database).
udt\_schema	Name of the column data type’s schema.
udt\_name	Name of the column data type.
scope\_catalog	Always NULL (unsupported by CockroachDB).
scope\_schema	Always NULL (unsupported by CockroachDB).
scope\_name	Always NULL (unsupported by CockroachDB).
maximum\_cardinality	Always NULL (unsupported by CockroachDB).
dtd\_identifier	Always NULL (unsupported by CockroachDB).
is\_self\_referencing	Always NULL (unsupported by CockroachDB).
is\_identity	Whether or not the column is an identity column (always NO).
identity\_generation	Always NULL (unsupported by CockroachDB).
identity\_start	If the column is an identity column, then the start value of the internal sequence, else NULL.
identity\_increment	If the column is an identity column, then the increment of the internal sequence, else NULL.
identity\_maximum	If the column is an identity column, then the maximum value of the internal sequence, else NULL.
identity\_minimum	If the column is an identity column, then the minimum value of the internal sequence, else NULL.
identity\_cycle	If the column is an identity column, then YES if the internal sequence cycles or NO if it does not; otherwise NULL.
is\_generated	Whether or not the column is generated (i.e., a ). Possible values: YES or NO.
generation\_expression	The expression used for computing the column value in a computed column.
is\_updatable	Whether or not the column is able to be updated. Possible values: YES or NO.
is\_hidden	Whether or not the column is hidden. Possible values: YES or NO.
crdb\_sql\_type	of the column.

column_privileges

column_privileges identifies all privileges granted on columns to or by a currently enabled role. There is one row for each combination of grantor, grantee, and column (defined by table_catalog, table_schema, table_name, and column_name).

Column	Description
grantor	Name of the role that granted the privilege.
grantee	Name of the role that was granted the privilege.
table\_catalog	Name of the database containing the table that contains the column (always the current database).
table\_schema	Name of the schema containing the table that contains the column.
table\_name	Name of the table.
column\_name	Name of the column.
privilege\_type	Name of the .
is\_grantable	Always NULL (unsupported by CockroachDB).

column_udt_usage

column_udt_usage identifies all columns that use data types owned by a currently-enabled role.

Column	Description
udt\_catalog	Name of the database in which the column data type is defined (always the current database).
udt\_schema	Name of the schema in which the column data type is defined.
udt\_name	Name of the column data type.
table\_catalog	Name of the database containing the table (always the current database).
table\_schema	Name of the schema containing the table.
table\_name	Name of the table.
column\_name	Name of the column.

constraint_column_usage

constraint_column_usage identifies all columns in a database that are used by some .

Column	Description
table\_catalog	Name of the database that contains the table that contains the column that is used by some constraint.
table\_schema	Name of the schema that contains the table that contains the column that is used by some constraint.
table\_name	Name of the table that contains the column that is used by some constraint.
column\_name	Name of the column that is used by some constraint.
constraint\_catalog	Name of the database that contains the constraint.
constraint\_schema	Name of the schema that contains the constraint.
constraint\_name	Name of the constraint.

enabled_roles

The enabled_roles view identifies enabled roles for the current user. This includes both direct and indirect roles.

Column	Description
role\_name	Name of a role.

key_column_usage

key_column_usage identifies columns with , , or constraints.

Column	Description
constraint\_catalog	Name of the database containing the constraint.
constraint\_schema	Name of the schema containing the constraint.
constraint\_name	Name of the constraint.
table\_catalog	Name of the database containing the constrained table.
table\_schema	Name of the schema containing the constrained table.
table\_name	Name of the constrained table.
column\_name	Name of the constrained column.
ordinal\_position	Ordinal position of the column within the constraint (begins at 1).
position\_in\_unique\_constraint	For foreign key constraints, ordinal position of the referenced column within its uniqueness constraint (begins at 1).

referential_constraints

referential_constraints identifies all referential () constraints.

Column	Description
constraint\_catalog	Name of the database containing the constraint.
constraint\_schema	Name of the schema containing the constraint.
constraint\_name	Name of the constraint.
unique\_constraint\_catalog	Name of the database containing the UNIQUE or PRIMARY KEY constraint that the foreign key constraint references (always the current database).
unique\_constraint\_schema	Name of the schema containing the UNIQUE or PRIMARY KEY constraint that the foreign key constraint references.
unique\_constraint\_name	Name of the UNIQUE or PRIMARY KEY constraint.
match\_option	Match option of the foreign key constraint: FULL, PARTIAL, or NONE.
update\_rule	Update rule of the foreign key constraint: CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION.
delete\_rule	Delete rule of the foreign key constraint: CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION.
table\_name	Name of the table containing the constraint.
referenced\_table\_name	Name of the table containing the UNIQUE or PRIMARY KEY constraint that the foreign key constraint references.

role_table_grants

role_table_grants identifies which have been granted on tables or views where the grantor or grantee is a currently enabled role. This table is identical to table_privileges.

Column	Description
grantor	Name of the role that granted the privilege.
grantee	Name of the role that was granted the privilege.
table\_catalog	Name of the database containing the table.
table\_schema	Name of the schema containing the table.
table\_name	Name of the table.
privilege\_type	Name of the .
is\_grantable	TRUE if the grantee has the grant option on the object; FALSE if not.
with\_hierarchy	Always NULL (unsupported by CockroachDB).

schema_privileges

schema_privileges identifies which have been granted to each user at the database level.

Column	Description
grantee	Username of user with grant.
table\_catalog	Name of the database containing the constrained table.
table\_schema	Name of the schema containing the constrained table.
privilege\_type	Name of the .
is\_grantable	Always NULL (unsupported by CockroachDB).

schemata

schemata identifies the database’s schemas.

Column	Description
table\_catalog	Name of the database.
table\_schema	Name of the schema.
default\_character\_set\_name	Always NULL (unsupported by CockroachDB).
sql\_path	Always NULL (unsupported by CockroachDB).

sequences

sequences identifies defined in a database.

Column	Description
sequence\_catalog	Name of the database that contains the sequence.
sequence\_schema	Name of the schema that contains the sequence.
sequence\_name	Name of the sequence.
data\_type	The data type of the sequence.
numeric\_precision	The (declared or implicit) precision of the sequence data\_type.
numeric\_precision\_radix	The base of the values in which the columns numeric\_precision and numeric\_scale are expressed. The value is either 2 or 10.
numeric\_scale	The (declared or implicit) scale of the sequence data\_type. The scale indicates the number of significant digits to the right of the decimal point. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column numeric\_precision\_radix.
start\_value	The first value of the sequence.
minimum\_value	The minimum value of the sequence.
maximum\_value	The maximum value of the sequence.
increment	The value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence.
cycle\_option	Currently, all sequences are set to NO CYCLE and the sequence will not wrap.

session_variables

session_variables contains information about the for your session. session_variables contains a variable column and a value column. The value column corresponds to the output of the statement.

For a list of the session variables, see .

For a list of the session variables that have been updated from default values, see .

statistics

statistics identifies table .

Column	Description
table\_catalog	Name of the database that contains the constrained table.
table\_schema	Name of the schema that contains the constrained table.
table\_name	Name of the table.
non\_unique	NO if the index was created with the UNIQUE constraint; YES if the index was not created with UNIQUE.
index\_schema	Name of the schema that contains the index.
index\_name	Name of the index.
seq\_in\_index	Ordinal position of the column within the index (begins at 1).
column\_name	Name of the column being indexed.
collation	Always NULL (unsupported by CockroachDB).
cardinality	Always NULL (unsupported by CockroachDB).
direction	ASC (ascending) or DESC (descending) order.
storing	YES if column is ; NO if it’s indexed or implicit.
implicit	YES if column is implicit (i.e., it is not specified in the index and not stored); NO if it’s indexed or stored.

table_constraints

table_constraints identifies applied to tables.

Column	Description
constraint\_catalog	Name of the database containing the constraint.
constraint\_schema	Name of the schema containing the constraint.
constraint\_name	Name of the constraint.
table\_catalog	Name of the database containing the constrained table.
table\_schema	Name of the schema containing the constrained table.
table\_name	Name of the constrained table.
constraint\_type	Type of : CHECK, FOREIGN KEY, PRIMARY KEY, or UNIQUE. NOT NULL constraints appear as CHECK constraints in this column.
is\_deferrable	YES if the constraint can be deferred; NO if not.
initially\_deferred	YES if the constraint is deferrable and initially deferred; NO if not.

table_privileges

table_privileges identifies which have been granted to each user at the table level.

Column	Description
grantor	Always NULL (unsupported by CockroachDB).
grantee	Username of the user with grant.
table\_catalog	Name of the database that the grant applies to.
table\_schema	Name of the schema that the grant applies to.
table\_name	Name of the table that the grant applies to.
privilege\_type	Type of : SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, or TRIGGER.
is\_grantable	TRUE if the grantee has the grant option on the object; FALSE if not.
with\_hierarchy	Always NULL (unsupported by CockroachDB).

tables

tables identifies tables and views in the database.

Column	Description
table\_catalog	Name of the database that contains the table.
table\_schema	Name of the schema that contains the table.
table\_name	Name of the table.
table\_type	Type of the table: BASE TABLE for a normal table, VIEW for a view, or SYSTEM VIEW for a view created by CockroachDB.
version	Version number of the table; versions begin at 1 and are incremented each time an ALTER TABLE statement is issued on the table. Note that this column is an experimental feature used for internal purposes inside CockroachDB and its definition is subject to change without notice.

type_privileges

type_privileges contains information about privileges on the user-defined types in the current database.

Column	Description
grantee	Username of user with privilege grant.
type\_catalog	Name of the database that contains the type (always the current database).
type\_schema	Name of the schema that contains the type.
type\_name	Name of the type.
privilege\_type	Type of .

user_privileges

user_privileges identifies global .

Column	Description
grantee	Username of user with grant.
table\_catalog	Name of the database that the privilege applies to.
privilege\_type	Type of .
is\_grantable	Always NULL (unsupported by CockroachDB).

views

views identifies in the database.

Column	Description
table\_catalog	Name of the database that contains the view.
table\_schema	Name of the schema that contains the view.
table\_name	Name of the view.
view\_definition	AS clause used to .
check\_option	Always NULL (unsupported by CockroachDB).
is\_updatable	Always NULL (unsupported by CockroachDB).
is\_insertable\_into	Always NULL (unsupported by CockroachDB).
is\_trigger\_updatable	Always NULL (unsupported by CockroachDB).
is\_trigger\_deletable	Always NULL (unsupported by CockroachDB).
is\_trigger\_insertable\_into	Always NULL (unsupported by CockroachDB).

Empty tables

For compatibility with third-party PostgreSQL and MySQL tooling, information_schema includes the following empty tables:

• attributes
• check_constraint_routine_usage
• column_column_usage
• column_domain_usage
• column_options
• column_statistics
• columns_extensions
• constraint_table_usage
• data_type_privileges
• domain_constraints
• domain_udt_usage
• domains
• element_types
• engines
• events
• files
• foreign_data_wrapper_options
• foreign_data_wrappers
• foreign_server_options
• foreign_servers
• foreign_table_options
• foreign_tables
• information_schema_catalog_name
• keywords
• optimizer_trace
• parameters
• partitions
• plugins
• processlist
• profiling
• resource_groups
• role_column_grants
• role_routine_grants
• role_udt_grants
• role_usage_grants
• routines
• routine_privileges
• schemata_extensions
• sql_features
• sql_implementation_info
• sql_parts
• sql_sizing
• st_geometry_columns
• st_spatial_reference_systems
• st_units_of_measure
• table_constraints_extensions
• tables_extensions
• tablespaces
• tablespaces_extensions
• transforms
• triggered_update_columns
• triggers
• udt_privileges
• usage_privileges
• user_attributes
• user_defined_types
• user_mapping_options
• user_mappings
• view_column_usage
• view_routine_usage
• view_table_usage

Querying information_schema tables

You can run on the tables in information_schema.

The information_schema views typically represent objects that the current user has privilege to access. To ensure you can view all the objects in a database, access it as a user with .

Unless specified otherwise, queries to information_schema assume the .

For example, to retrieve all columns from the table_constraints table:

> SELECT * FROM movr.information_schema.table_constraints;

  constraint_catalog | constraint_schema |       constraint_name        | table_catalog | table_schema |         table_name         | constraint_type | is_deferrable | initially_deferred
---------------------+-------------------+------------------------------+---------------+--------------+----------------------------+-----------------+---------------+---------------------
  movr               | public            | users_pkey                   | movr          | public       | users                      | PRIMARY KEY     | NO            | NO
  movr               | public            | 3426283741_53_1_not_null     | movr          | public       | users                      | CHECK           | NO            | NO
  movr               | public            | 3426283741_53_2_not_null     | movr          | public       | users                      | CHECK           | NO            | NO
  movr               | public            | vehicles_pkey                | movr          | public       | vehicles                   | PRIMARY KEY     | NO            | NO
...
(25 rows)

And to retrieve specific columns from the table_constraints table:

> SELECT table_name, constraint_name FROM movr.information_schema.table_constraints;

          table_name         |       constraint_name
-----------------------------+-------------------------------
  users                      | primary
  users                      | 3426283741_53_1_not_null
  users                      | 3426283741_53_2_not_null
  vehicles                   | primary
...
(25 rows)

See also