How to Perform a CockroachDB DESCRIBE TABLE

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’ve spent any time working with MySQL, you may be familiar with the DESCRIBE TABLE command, which is used to provide details about the columns in a table. While there is no CockroachDB DESCRIBE TABLE command, an effective equivalent exists: the SHOW COLUMNS command. In this article, we’ll discuss the CockroachDB SHOW COLUMNS command and explain how it can deliver the same results as DESCRIBE TABLE.

Prerequisites

In order to follow along with this tutorial, you’ll need to have a CockroachDB cluster installed and working on your machine. Use the bash command cockroach version in the terminal to display the version number for your CockroachDB installation.

CockroachDB DESCRIBE TABLE equivalent

As we mentioned earlier, there’s no DESCRIBE TABLE command in CockroachDB. Instead, you can use the SHOW COLUMNS command to obtain the same type of information: descriptions of the columns in the table, including the name of each column, the data type, the default value and whether or not it is nullable.

The basic syntax of the SHOW COLUMNS command is:

1
SHOW COLUMNS FROM TABLE_NAME [WITH] [COMMENT];

Start a single-node cluster

We’ll start by creating and running a single-node CockroachDB cluster on a local machine:

1
2
3
4
5
cockroach start-single-node \
--insecure
--listen-addr=localhost:26257 \
--http-addr=localhost:8080 \
--background

Access the built-in shell for CockroachDB

Next, we’ll use the terminal to access and enter the built-in SQL shell for CockroachDB. The command we’ll use is shown below:

1
cockroach SQL --insecure

Once we’re in the shell, we can execute SQL commands such as the CockroachDB SHOW COLUMNS command.

Before we test out that command, we’ll create a database and a table to use in our examples. We’ll make sure that our table has a variety of columns so that we can view this column information when we execute the SHOW COLUMNS command.

Create a database and table in CockroachDB

To create a database in CockroachDB, we’ll use the following SQL statement:

1
CREATE DATABASE demo;
  • After we create this database, we can execute the command SHOW DATABASES to display a list of all databases in our cluster.
  • We can then use the command USE followed by the database name to connect to the database and enter any SQL queries.

Here’s the command we’ll use to create a CockroachDB table:

1
2
3
4
5
6
CREATE TABLE demo(
    id INT PRIMARY KEY NOT NULL,
    str_col VARCHAR(50),
    int_col INT NOT NULL,
    bool_col BOOL
);

NOTE Execute the command SHOW TABLES to list all of the tables in your database.

Show columns of a table

To describe a table in CockroachDB, use the following command:

1
SHOW COLUMNS FROM demo;

The result of this command will look something like this:

1
2
3
4
5
6
7
8
9
  column_name |  data_type  | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-------------+-------------+----------------+-----------------------+-----------+-----------+
  id          | INT8        |    false    | NULL           |                       | {primary} |   false    
  str_col     | VARCHAR(50) |    true     | NULL           |                       | {}        |   false    
  int_col     | INT8        |    false    | NULL           |                       | {}        |   false    
  bool_col    | BOOL        |    true     | NULL           |                       | {}        |   false    
(4 rows)

Time: 33.448584ms

We can see that the SHOW COLUMNS statement returns a great deal of information about the columns in our table.

We can add a comment to the column str_col using the command shown below:

1
COMMENT ON COLUMN demo.str_col IS 'Show comment in column';

If we use the SHOW COLUMNS command again, we’ll see that our comment was added:

1
2
3
4
5
6
7
  column_name |  data_type  | is_nullable | column_default | generation_expression |  indices  | is_hidden |        comment          
+-------------+-------------+-------------+----------------+-----------------------+-----------+-----------+------------------------+
  id          | INT8        |    false    | NULL           |                       | {primary} |   false   | NULL                    
  str_col     | VARCHAR(50) |    true     | NULL           |                       | {}        |   false   | Show comment in column  
  int_col     | INT8        |    false    | NULL           |                       | {}        |   false   | NULL                    
  bool_col    | BOOL        |    true     | NULL           |                       | {}        |   false   | NULL                    
(4 rows)

Conclusion

When you’re administering a CockroachDB database, it’s important to know how to obtain information about a table. Since there’s no CockroachDB DESCRIBE TABLE command, the SHOW COLUMNS command is used instead to obtain information about the columns in a table. In this article, we explained how to use the SHOW COLUMNS command and provided some helpful examples. If you’ve followed along with our tutorial, you’ll be prepared to retrieve table information in your own CockroachDB database.

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.