Information Schema in CockroachDB Schema
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