Information Schema

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.

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.

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

• SHOW
• SHOW COLUMNS
• SHOW CONSTRAINTS
• SHOW CREATE TABLE
• SHOW CREATE VIEW
• SHOW DATABASES
• SHOW GRANTS
• SHOW INDEX
• SHOW TABLES