Skip to main content
The information_schema system catalog 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.

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 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 Name Resolution 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.

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.

character_sets

character_sets identifies the character sets available in the current database.

check_constraints

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

collations

collations identifies the collations available in the current database.

collation_character_set_applicability

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

columns

columns contains information about the columns in each table.

column_domain_usage

column_domain_usage identifies all columns that use a domain data type.

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_udt_usage

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

constraint_column_usage

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

crdb_cluster_active_session_history

The crdb_cluster_active_session_history view tracks cluster-wide in-memory Active Session History samples. For more information, refer to the ASH table reference.

crdb_databases

The crdb_databases view contains one row for each database, with the database’s owner, survival goal, and zone configuration. This view is a stable interface intended for programmatic introspection of databases. The view returns only databases that the current user has a privilege on, plus the current database.

crdb_index_usage_statistics

The crdb_index_usage_statistics table contains one row for each index in the current database, with read and write usage statistics collected from every node in the cluster since index statistics were last reset. Querying this table is expensive because it contacts every node in the cluster. Each node keeps its statistics in memory, so a node’s statistics are lost when the node restarts. To reset index statistics for the entire cluster, use Reset all index stats in the DB Console, or call the crdb_internal.reset_index_usage_stats() function. To stop collecting index usage statistics, set the sql.metrics.index_usage_stats.enabled cluster setting to false. Usage is counted per statement, not per row. For example, an INSERT statement that writes 100 rows to an index counts as one write to that index, not 100. Statements that CockroachDB runs internally are not counted. EXPLAIN statements are not counted, but EXPLAIN ANALYZE statements are. An INSERT, UPSERT, UPDATE, or DELETE statement counts as a write to the table’s primary index and to each secondary index that the statement maintains. An UPDATE statement maintains a secondary index only if it changes a column that the index stores, with two exceptions: an UPDATE that changes a primary key column maintains every secondary index, and every UPDATE maintains each partial index. Writes are counted when the statement’s query plan is built, so a statement that later fails is still counted. For an example query, refer to Fix slow writes.

crdb_indexes

The crdb_indexes view contains one row for each index of each table, with the key span that the index occupies. Indexes that are still being created or dropped are not included. This view is a stable interface intended for programmatic introspection of indexes. The view returns only indexes in databases that the current user has a privilege on. To find the ranges that store an index, join the index’s key span to the key spans in crdb_ranges.

crdb_jobs

The crdb_jobs view contains metadata for each job on the cluster. This view is a stable interface intended for programmatic monitoring of jobs. To monitor jobs in the DB Console, use the Jobs page. The view returns only jobs that the current user owns, including through membership in the owning role, or can view with the VIEWJOB privilege. Jobs that have reached the succeeded, failed, or canceled state are removed from the view after the retention period set by the jobs.retention_time cluster setting. crdb_jobs reads only job metadata and is the least expensive way to query jobs programmatically. To include each job’s current progress, use crdb_jobs_with_progress. To retrieve the message history or progress history of a specific job, use the information_schema.crdb_job_messages(job_id) and information_schema.crdb_job_progress_history(job_id) system info functions. The job_id argument is required. An unknown job_id, or a job that is not visible to the current user, returns zero rows. Fetching a job’s history can cause contention, so use these functions to inspect specific jobs. Do not call them on large numbers of jobs at once.

crdb_jobs_with_progress

The crdb_jobs_with_progress view contains the same job metadata as crdb_jobs, along with each job’s current progress and status message. Because it joins additional system tables, it is more expensive to query than crdb_jobs. The view returns only jobs that the current user owns, including through membership in the owning role, or can view with the VIEWJOB privilege. Jobs that have reached the succeeded, failed, or canceled state are removed from the view after the retention period set by the jobs.retention_time cluster setting.

crdb_node_active_session_history

The crdb_node_active_session_history view tracks in-memory Active Session History samples from the local node. It has the same columns as crdb_cluster_active_session_history. For more information, refer to the ASH table reference.

crdb_nodes

The crdb_nodes table contains one row for each node that has joined the cluster, including dead and decommissioned nodes, with the node’s health status, locality, and liveness state. This table is a stable interface intended for programmatic monitoring of nodes. Reading crdb_nodes requires the VIEWCLUSTERMETADATA system privilege. When queried from a virtual cluster, crdb_nodes also requires that the virtual cluster have the can_view_node_info capability. crdb_nodes reads node status from the cluster’s key-value layer, so it cannot be queried while the cluster is unavailable. The address, host, build_tag, region, zone, locality_raw, range_count, lease_count, started_at, and cluster_name columns come from the node’s status record. These columns are NULL for a node whose status record has been removed, such as a decommissioned node.

crdb_persisted_active_session_history

The crdb_persisted_active_session_history view tracks persisted, cluster-wide Active Session History samples. It has the same columns as crdb_cluster_active_session_history. For more information, refer to the ASH table reference.

crdb_ranges

The crdb_ranges view contains one row for each range, with the range’s key span and the stores that hold its replicas. This view is a stable interface intended for programmatic monitoring of ranges. To read crdb_ranges, a user must have the admin role, the VIEWACTIVITY or VIEWACTIVITYREDACTED system privilege, or the ZONECONFIG privilege on at least one database or table. When queried from a virtual cluster, crdb_ranges returns only that virtual cluster’s ranges. To find the table or index that a range belongs to, join the range’s key span to the key spans in crdb_tables or crdb_indexes. For an example, refer to Check cluster health.

crdb_statement_execution_insights

The crdb_statement_execution_insights view contains persisted, cluster-wide statement execution insights for user workloads. Each row is a statement execution that CockroachDB flagged as slow or failed. Statements run by internal applications, whose names begin with $ internal, are not included. Each node writes the execution insights in its in-memory store to system tables at the interval set by the sql.insights.flush.interval cluster setting. An insight appears in the view after the next write. An insight that the node evicts from its in-memory store before the next write is not persisted. The size of the in-memory store is set by the sql.insights.execution_insights_capacity cluster setting. To stop persisting insights, set the sql.insights.flush.enabled cluster setting to false. Persisted insights are deleted after the retention period set by the sql.insights.compaction.retention_period cluster setting, which defaults to 168 hours (7 days). To query the view, you must be an admin user or have the VIEWACTIVITY or VIEWACTIVITYREDACTED system privilege. For other users, the view returns no rows. Until the upgrade to v26.4 is finalized, the view returns no rows.

crdb_stores

The crdb_stores view contains one row for each store, with the store’s range and lease counts, range health, and disk capacity. This view is a stable interface intended for programmatic monitoring of stores. Reading crdb_stores requires the VIEWCLUSTERMETADATA system privilege. crdb_stores is not supported from a virtual cluster. Each query to crdb_stores requests store status from every node in the cluster, which makes it expensive to run. A node can have more than one store, so a store’s store_id does not always match its node_id. To find the node that holds a store, use node_id.

crdb_tables

The crdb_tables view contains one row for each table, with the table’s key span, estimated row count, and zone configuration. Tables that are still being created or dropped, and offline tables, are not included. This view is a stable interface intended for programmatic introspection of tables. The zone_config_sql column shows only a zone configuration set directly on the table. The num_replicas, constraints, and lease_preferences columns show the effective values for the table, resolved through the table’s zone configuration, its database’s zone configuration, and the RANGE default zone configuration. For details, refer to How zone config inheritance works. For a table that the current user has no privilege on, the zone_config_sql, num_replicas, constraints, and lease_preferences columns are NULL. The key span columns and estimated_row_count are also NULL for a table in a database that the current user has no privilege on. Virtual tables, such as the tables in information_schema and pg_catalog, have no key span. For these tables, the key span, estimated_row_count, num_replicas, constraints, and lease_preferences columns are NULL.

crdb_transaction_execution_insights

The crdb_transaction_execution_insights view contains persisted, cluster-wide transaction execution insights for user workloads. Each row is a transaction execution that CockroachDB flagged with an insight. Transactions run by internal applications, whose names begin with $ internal, are not included. Each node writes the execution insights in its in-memory store to system tables at the interval set by the sql.insights.flush.interval cluster setting. An insight appears in the view after the next write. An insight that the node evicts from its in-memory store before the next write is not persisted. The size of the in-memory store is set by the sql.insights.execution_insights_capacity cluster setting. To stop persisting insights, set the sql.insights.flush.enabled cluster setting to false. Persisted insights are deleted after the retention period set by the sql.insights.compaction.retention_period cluster setting, which defaults to 168 hours (7 days). To query the view, you must be an admin user or have the VIEWACTIVITY or VIEWACTIVITYREDACTED system privilege. For other users, the view returns no rows. Until the upgrade to v26.4 is finalized, the view returns no rows.

domain_constraints

domain_constraints identifies the CHECK and NOT NULL constraints on domain data types.

domain_udt_usage

domain_udt_usage identifies the base data type of each domain data type.

domains

domains identifies the domain data types in the database. The data type, length, precision, collation, and udt_* columns describe the domain’s base data type.

enabled_roles

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

key_column_usage

key_column_usage identifies columns with PRIMARY KEY, UNIQUE, or foreign key / REFERENCES constraints.

referential_constraints

referential_constraints identifies all referential (foreign key) constraints.

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.

schema_privileges

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

schemata

schemata identifies the database’s schemas.

sequences

sequences identifies sequences defined in a database.

session_variables

session_variables contains information about the session variable settings for your session. session_variables contains a variable column and a value column. The value column corresponds to the output of the SHOW {session variable} statement. For a list of the session variables, see supported variables. For a list of the session variables that have been updated from default values, see SHOW DEFAULT SESSION VARIABLES FOR ROLE.

statistics

statistics identifies table indexes.

table_constraints

table_constraints identifies constraints applied to tables.

table_privileges

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

tables

tables identifies tables and views in the database.

type_privileges

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

user_privileges

user_privileges identifies global privileges.

views

views identifies views in the database.

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_options
  • column_statistics
  • columns_extensions
  • constraint_table_usage
  • data_type_privileges
  • 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 SELECT queries 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 admin privileges.
Unless specified otherwise, queries to information_schema assume the current database.
For example, to retrieve all columns from the table_constraints table:
And to retrieve specific columns from the table_constraints table:

Check cluster health

The following queries use the crdb_nodes, crdb_stores, crdb_ranges, and crdb_tables tables to check the health of a cluster. To list the nodes that are not healthy, query crdb_nodes:
The output looks like the following:
In this output, node 2 has stopped renewing its liveness record. Node 3 is draining, so its status is draining even though it is still live. To list the stores that report under-replicated or unavailable ranges, query crdb_stores:
The output looks like the following:
To list the ranges that store the movr.rides table, with the number of replicas of each range and their localities, join crdb_ranges to crdb_tables on their key spans:
The output looks like the following:

See also