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 DATABASESto display a list of all databases in our cluster. - We can then use the command
USEfollowed 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