Information Schema

On this page Carat arrow pointing down
Warning:
CockroachDB v2.1 is no longer supported. For more details, see the Release Support Policy.

CockroachDB provides a virtual schema called information_schema that contains information about your database's tables, columns, indexes, and views. This information can be used for introspection and reflection.

The definition of information_schema is part of the SQL standard and can therefore be relied on to remain stable over time. This contrasts with CockroachDB's SHOW statements, which provide similar data and are meant to be stable in CockroachDB but not standardized. It also contrasts with the virtual schema crdb_internal, which reflects the internals of CockroachDB and may thus change across CockroachDB versions.

Note:

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 the root user.

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.

Object Information Schema Table Corresponding SHOW Statement
Columns columns SHOW COLUMNS
Constraints key_column_usage, referential_constraints, table_constraints SHOW CONSTRAINTS
Databases schemata SHOW DATABASE
Indexes statistics SHOW INDEX
Privileges schema_privileges, table_privileges SHOW GRANTS
Roles role_table_grants SHOW ROLES
Sequences sequences SHOW CREATE SEQUENCE
Tables tables SHOW TABLES
Views tables, views SHOW CREATE

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 SQL views in that they are not showing data created from the content of other tables. Instead, CockroachDB generates the data for virtual tables when they are accessed.

Currently, there are some information_schema tables that are empty but provided for compatibility:

  • routines
  • parameters
Note:

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

administrable_role_authorizations

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

Column Description
grantee The name of the user to which this role membership was granted (always the current user).

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.

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.
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 NOT NULL constraint).
data_type 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 Always NULL (unsupported by CockroachDB).
character_set_catalog Always NULL (unsupported by CockroachDB).
character_set_schema Always NULL (unsupported by CockroachDB).
character_set_name Always NULL (unsupported by CockroachDB).
generation_expression The expression used for computing the column value in a computed 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 privilege.
is_grantable Always NULL (unsupported by CockroachDB).

constraint_column_usage

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

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 PRIMARY KEY, UNIQUE, or foreign key / REFERENCES 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 (Foreign Key) 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 privileges 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 privilege.
is_grantable Always NULL (unsupported by CockroachDB).
with_hierarchy Always NULL (unsupported by CockroachDB).

schema_privileges

schema_privileges identifies which privileges 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 privilege.
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 sequences 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.

statistics

statistics identifies table indexes.

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 database 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 stored; 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 constraints 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 constraint: CHECK, foreign key, PRIMARY KEY, or UNIQUE.
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 privileges 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 privilege: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, or TRIGGER.
is_grantable Always NULL (unsupported by CockroachDB).
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.

user_privileges

user_privileges identifies global privileges.

Note:
Currently, CockroachDB does not support global privileges for non-root users. Therefore, this view contains global privileges only for root.
Column Description
grantee Username of user with grant.
table_catalog Name of the database that the privilege applies to.
privilege_type Type of privilege.
is_grantable Always NULL (unsupported by CockroachDB).

views

views identifies views 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 create the view.
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).

Examples

Retrieve all columns from an information schema table

icon/buttons/copy
> SELECT * FROM db_name.information_schema.table_constraints;
+--------------------+-------------------+-----------------+---------------+--------------+-------------+-----------------+---------------+--------------------+
| constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name  | constraint_type | is_deferrable | initially_deferred |
+--------------------+-------------------+-----------------+---------------+--------------+-------------+-----------------+---------------+--------------------+
| jsonb_test         | public            | primary         | jsonb_test    | public       | programming | PRIMARY KEY     | NO            | NO                 |
+--------------------+-------------------+-----------------+---------------+--------------+-------------+-----------------+---------------+--------------------+

Retrieve specific columns from an information schema table

icon/buttons/copy
> SELECT table_name, constraint_name FROM db_name.information_schema.table_constraints;
+-------------+-----------------+
| table_name  | constraint_name |
+-------------+-----------------+
| programming | primary         |
+-------------+-----------------+

See also


Yes No
On this page

Yes No