Information Schema in CockroachDB Schema

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

Introduction

In this article we’ll discuss CockroachDB schemas with great demo examples.

Prerequisites

Must possess basic knowledge on CockroachDB schema and make sure that there is an CockroachDB cluster running on your computer.

The information schema in cockroachdb

The notion of a database schema is part of the SQL framework, which ensures that it is scalable and stays secure. It compares with other objects, such as pg catalog, which is not part of the SQL specification and discusses different application problems.

CockroachDB reflects the cluster schema in the information schema database, which includes read-only views that you may use to introspect the database columns, tables, views and indexes.

Start a single node cluster

Before we proceed to the information schema of cockroachdb, we must start a single node cluster of CockroachDB.

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

And then open the SQL built in shell for CockroachDB

1
cockroach sql --insecure --host=localhost:26257

Enter and connect to an specific database using the command.

1
USE database_name;

Information schema on objects

You may either read from the associated information schema view or use one of the display statements of CockroachDB to conduct introspection on objects.

  • COLUMNS – to display an information schema view of a column in a table you can execute the command.
1
SHOW COLUMNS FROM TABLE_NAME;

For example :

1
2
3
4
5
  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  dept_id     | INT8      |    true     | NULL           |                       | {}        |   false    
  department  | STRING    |    true     | NULL           |                       | {}        |   false    
  rowid       | INT8      |    false    | unique_rowid() |
  • CONSTRAINTS – to display an information schema view of the key column usage and table constraints you can execute the command.
1
SHOW CONSTRAINTS FROM TABLE_NAME;

For example :

1
2
3
   table_name  | constraint_name | constraint_type |         details          | validated  
+--------------+-----------------+-----------------+--------------------------+-----------+
  compensation | primary         | PRIMARY KEY     | PRIMARY KEY (emp_id ASC) |   true
  • DATABASES – to display the information schema view of a database schemata in CockroachDB you can execute the command.
1
SHOW DATABASES;

For example :

1
2
3
4
5
6
7
  database_name  
+---------------+
  defaultdb      
  demo          
  postgres      
  somedb        
  system

This will also list all of the database name that is save on your single node cluster in CockroachDB.

  • INDEXES – to display an information schema view on the statistics of a table in CockroachDB you can execute the command.
1
SHOW INDEX FROM TABLE_NAME;

For example :

1
2
3
  table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit  
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
  department | primary    |   false    |            1 | rowid       | ASC       |  false  |  false
  • PRIVILEGES – to display an information schema view on the schema privileges or the table privileges in CockroachDB you can use the command.
1
SHOW GRANTS ON [database_name], [TABLE_NAME], FOR user_name;

For example :

1
SHOW GRANTS ON DATABASE demo;

The result is something like this :

1
2
3
4
5
6
7
8
9
10
  database_name |    schema_name     | grantee | privilege_type  
+---------------+--------------------+---------+----------------+
  demo          | crdb_internal      | admin   | ALL            
  demo          | crdb_internal      | root    | ALL            
  demo          | information_schema | admin   | ALL            
  demo          | information_schema | root    | ALL            
  demo          | pg_catalog         | admin   | ALL            
  demo          | pg_catalog         | root    | ALL            
  demo          | public             | admin   | ALL            
  demo          | public             | root    | ALL
1
SHOW GRANTS ON TABLE department;

The output would be :

1
2
3
4
  database_name | schema_name | table_name | grantee | privilege_type  
+---------------+-------------+------------+---------+----------------+
  demo          | public      | department | admin   | ALL            
  demo          | public      | department | root    | ALL
  • TABLES – to display an information schema view on table in CockroachDB you can execute the command.
1
SHOW TABLES FROM database_name;

For example :

1
2
3
4
5
6
7
   table_name  
+--------------+
  compensation  
  demo          
  department    
  employee      
  incentives
  • VIEWS – to display an information schema on tables and views in CockroachDB you can execute the command.
1
SHOW CREATE VIEW view_name;

For example :

1
2
3
           table_name          |                                   create_statement                                    
+------------------------------+--------------------------------------------------------------------------------------+
  demo.public.employees_salary | CREATE VIEW employees_salary (salary) AS SELECT salary FROM demo.public.compensation

Conclusion

We hope this was an informative tutorial for you and your application. We discussed CockroachDB schema and hope it has left you with a deeper understanding of the topic. If you’re looking for tutorials on CockroachDB, we have many articles in our knowledge base.

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.