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:
1 2 3 4 5 | cockroach start-single-node \ --insecure \ --listen-addr=localhost:26257 \ --http-addr=localhost:8080 \ --background |
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:
1 | 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:
1 2 3 4 | root@:26257/defaultdb> CREATE DATABASE demo; CREATE DATABASE TIME: 543.443586ms |
We can then enter the database with the USE
command followed by the database name:
1 | USE demo; |
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:
1 | 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 demo
:
1 2 3 4 5 6 7 8 9 10 | root@:26257/demo> SHOW TABLES; TABLE_NAME +------------+ department employee incentives salary (4 ROWS) TIME: 3.600932ms |
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:
1 | SHOW SCHEMAS; |
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:
1 | 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
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:
1 | SHOW TABLES FROM information_schema; |
The result will look something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | TABLE_NAME +-----------------------------------+ administrable_role_authorizations applicable_roles check_constraints column_privileges COLUMNS constraint_column_usage enabled_roles key_column_usage parameters referential_constraints role_table_grants routines schema_privileges schemata sequences statistics table_constraints table_privileges TABLES user_privileges views (21 ROWS) TIME: 4.619018ms |
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:
1 | SELECT * FROM information_schema.schema_privileges; |
This table contains the following columns that describe the user role permissions:
1 2 | grantee | table_catalog | table_schema | privilege_type | is_grantable |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started