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:

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:

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:

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

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:

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

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:

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

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:

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:

The result will look something like this:

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:

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

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.