Information Schema

On this page Carat arrow pointing down

CockroachDB represents your cluster's schema in a database called information_schema, which contains read-only views that you can use for introspection into your database's tables, columns, indexes, and views.

This notion of an information schema is part of the SQL standard, which means it is portable and will remain stable. This contrasts with other objects like pg_catalog, which is not part of the SQL standard and handles specific implementation issues.

Note:
The information_schema views typically represent objects that the current user has privilege to access. To ensure you can view your cluster's entire schema, access it as the root user.

Objects

To perform introspection on objects, you can either read from the related information_schema view or use one of CockroachDB's SHOW statements.

Object Information Schema View SHOW .
Columns columns SHOW COLUMNS
Constraints key_column_usage, table_constraints SHOW CONSTRAINTS
Databases schemata SHOW DATABASE
Indexes statistics SHOW INDEX
Privileges schema_privileges, table_privileges SHOW GRANTS
Tables tables SHOW TABLES
Views tables, views SHOW CREATE VIEW

Views

The information_schema database is comprised of many views representing your cluster's schema, each of which is detailed below.

columns

The columns view contains information about the columns in each table.

Column Description
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
TABLE_SCHEMA Name of the database 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_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).

key_column_usage

The key_column_usage view identifies columns with Primary Key, Unique, or Foreign Key constraints.

Column Description
CONSTRAINT_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
CONSTRAINT_SCHEMA Name of the database containing the constraint.
CONSTRAINT_NAME Name of the constraint.
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
TABLE_SCHEMA Name of the database that contains 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 Unique constraint (begins at 1).

schema_privileges

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

Column Description
GRANTEE Username of user with grant.
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
TABLE_SCHEMA Name of the database that contains the constrained table.
PRIVILEGE_TYPE Name of the privilege.
IS_GRANTABLE Always NULL (unsupported by CockroachDB).

schemata

The schemata view identifies the cluster's databases.

Column Description
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
SCHEMA_NAME Name of the database.
DEFAULT_CHARACTER_SET_NAME Always NULL (unsupported by CockroachDB).
SQL_PATH Always NULL (unsupported by CockroachDB).

statistics

The statistics view identifies table's indexes.

Column Description
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
TABLE_SCHEMA Name of the database that contains the constrained table.
TABLE_NAME Name of the table .
NON_UNIQUE false if the index was created by a Unique constraint; true if the index was not created by a Unique constraint.
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 true if column is stored; false if it's indexed.

table_constraints

The table_constraints view identifies constraints applied to tables.

Column Description
CONSTRAINT_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
CONSTRAINT_SCHEMA Name of the database that contains the constraint.
CONSTRAINT_NAME Name of the constraint.
TABLE_SCHEMA Name of the database that contains the constrained table.
TABLE_NAME Name of the constrained table.
CONSTRAINT_TYPE Type of constraint: CHECK, FOREIGN KEY, PRIMARY KEY, or UNIQUE.

table_privileges

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

Column Description
GRANTOR Always NULL (unsupported by CockroachDB).
GRANTEE Username of user with grant.
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
TABLE_SCHEMA Name of the database 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

The tables view identifies tables and views in the cluster.

Column Description
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
TABLE_SCHEMA Name of the database 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.

user_privileges
New in v1.1

The user_privileges view 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 Always equal to def (CockroachDB does not support the notion of catalogs).
PRIVELEGE_TYPE Type of privilege.
IS_GRANTABLE Always NULL (unsupported by CockroachDB).

views

The views view identifies views in the cluster.

Column Description
TABLE_CATALOG Always equal to def (CockroachDB does not support the notion of catalogs).
TABLE_SCHEMA Name of the database that the view reads from.
TABLE_NAME Name of the table the view reads from.
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).

See Also


Yes No
On this page

Yes No

Product

Resources

Learn

Support Channels

Company

Get developer news