CockroachDB Show Table Schema

Introduction

If you’re using CockroachDB to store and manage your data, it’s important to understand schemas and how they work. CockroachDB uses four built-in table schemas: crdb_internal, information_schema, pg_catalog, and public. These schemas are server tables that enforce security limits designed to limit data access. As its name suggests, the public schema is applied to tables that are meant to be publicly available with few restrictions. In this article, we’ll show you how to use CockroachDB to show a table schema and obtain the information you need about a given table.

Prerequisites

Before proceeding with this tutorial, you’ll need to confirm that you have CockroachDB installed on your machine. Use the command cockroach version to return the build tag and version number of the database cluster that’s installed on your system.

Start a single cluster

We’ll begin by creating and running an insecure single cluster. We’ll use this to create a database and table and also to show the table schema:

cockroach start-single-node \
--insecure \
--listen-addr=localhost:26257 \
--http-addr=localhost:8080 \
--background

Let’s take a closer look at the syntax of the command we just executed:

  • The cockroach start-single-node command is used to start and create a local cluster on your machine. This cluster can be used to test various SQL queries within the built-in shell console.

  • The --insecure flag is used to create a cluster that offers privileges without entering a password and without authentication.

  • The --listen-addr=localhost:26257 flag indicates the default CockroachDB port that is used for client connections.

  • The --http-addr=localhost:8080 flag indicates the default CockroachDB port where clients listen on the user interface admin for HTTP requests.

  • The --background flag indicates that the local cluster will run automatically in the background when it is ready to accept requests.

We’ll use the built-in SQL shell in CockroachDB to access the local cluster we just created:

cockroach sql --insecure

Create a database and table

After we connect to the SQL shell console, we can create a database and table in CockroachDB.

To create a database in cockroachDB, we use the following syntax:

root@:26257/defaultdb> CREATE DATABASE demo;
CREATE DATABASE

TIME: 543.443586ms

We can then enter the database with the USE command followed by the database name:

USE demo;

NOTE: The USE command acts as an alias for the SET database = command for people who are accustomed to MySQL command syntax.

To create a table in CockroachDB, we’ll use the following command:

CREATE TABLE TABLE_NAME([COL_NAME + DATA_TYPE + CONSTRAINTS]);

At this point, we can use the SHOW TABLES schema to list the current schema tables within the database demo:

root@:26257/demo> SHOW TABLES;
  TABLE_NAME  
+------------+
  department  
  employee    
  incentives  
  salary      
(4 ROWS)

TIME: 3.600932ms

NOTE: You can also use the command \dt in a SQL command-line console to accomplish the same result.

CockroachDB Show Schemas

To get the CockroachDB schema names, we’ll use the following command:

SHOW SCHEMAS;

This command will list the crdb_internal, information_schema, pg_catalog, and public schemas for your databases.

Screenshot of SHOW SCHEMAS; CockroachDB show table schema

To list the table information for each schema, simply use the SHOW TABLES FROM SQL statement followed by the schema name. For example, let’s say we’d like to get the table information for the crdb_internal. We’d use the following command:

SHOW TABLES FROM crdb_internal;

Some schemas, such as pg_catalog, are not part of the SQL standard. These schemas are actually PostgreSQL system catalogs that CockroachDB utilizes so that it can be more compatible with Postgres data. The crdb_internal schema, however, is an internal schema designed for CockroachDB tables.

CockroachDB Information Schema

The information_schema is a SQL-standard “virtual” database schema that contains multiple information tables. These tables provide more detailed information on the current user of the database.

We can use the following SQL statement to get all of the tables from the information_schema schema database:

SHOW TABLES FROM information_schema;

The result will look something like this:

             TABLE_NAME              
+-----------------------------------+
  administrable_role_authorizations  
  applicable_roles                  
  check_constraints                  
  column_privileges                  
  COLUMNS                            
  constraint_column_usage            
  enabled_roles                      
  key_column_usage                  
  parameters                        
  referential_constraints            
  role_table_grants                  
  routines                          
  schema_privileges                  
  schemata                          
  sequences                          
  statistics                        
  table_constraints                  
  table_privileges                  
  TABLES                            
  user_privileges                    
  views                              
(21 ROWS)

TIME: 4.619018ms

To access information from any of the tables found in information_schema, just append the table name at the end with a period, as seen in the example below:

SELECT * FROM information_schema.schema_privileges;

This table contains the following columns that describe the user role permissions:

grantee | table_catalog |
table_schema | privilege_type | is_grantable

Screenshot of cockroachdb show table schema for information_schema schema_privileges

Conclusion

If you’re working with CockroachDB and need information about tables or schemas, it’s important to know how to obtain that information. In this article, we showed you how to use CockroachDB to show table schemas. With our examples to guide you, you’ll be able to access table and schema information in your own CockroachDB environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.