Generate DDL for a CockroachDB database
Summary: This page explains how to generate DDL for a CockroachDB database with SHOW CREATE statement, for use with the SQL Analyzer extension.
Extraction examples
The SHOW CREATE statement provided by CockroachDB generates the DDL statements by object type. See the full documentation of the statement here: https://www.cockroachlabs.com/docs/v21.2/show-create.
Show the CREATE DATABASE statement for a database
SHOW CREATE DATABASE <DATABASE_NAME>;
Show the statements needed to recreate all tables, views in the current database
SET DATABASE = <DATABASE_NAME>;
SHOW CREATE ALL TABLES;
Check if your code is valid SQL
For example, the following is not valid SQL, simply remove the useless details and add missing statements:
Mainframe alike script - NOK
create_statement
-----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE public.users (
id UUID NOT NULL,
city VARCHAR NOT NULL,
name VARCHAR NULL,
address VARCHAR NULL,
credit_card VARCHAR NULL,
CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
FAMILY "primary" (id, city, name, address, credit_card)
);
CREATE VIEW public.user_view (city, name) AS SELECT city, name FROM movr.public.users;
(2 rows)
The correct script should look like this:
OK script
SET DATABASE = movr;
CREATE TABLE public.users (
id UUID NOT NULL,
city VARCHAR NOT NULL,
name VARCHAR NULL,
address VARCHAR NULL,
credit_card VARCHAR NULL,
CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
FAMILY "primary" (id, city, name, address, credit_card)
);
CREATE VIEW public.user_view (city, name) AS SELECT city, name FROM movr.public.users;