How to Perform a CockroachDB DESCRIBE TABLE
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