CockroachDB Show Table Schema
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:
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.
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 \
Let’s take a closer look at the syntax of the command we just executed:
cockroach start-single-nodecommand 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.
--insecureflag is used to create a cluster that offers privileges without entering a password and without authentication.
--listen-addr=localhost:26257flag indicates the default CockroachDB port that is used for client connections.
--http-addr=localhost:8080flag indicates the default CockroachDB port where clients listen on the user interface admin for HTTP requests.
--backgroundflag 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;
We can then enter the database with the
USE command followed by the database name:
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
root@:26257/demo> SHOW TABLES;
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
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:
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
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:
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
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