The SHOW CREATE statement shows the CREATE statement for an existing table, view, or sequence.

Required privileges

The user must have any privilege on the target table, view, or sequence.

Synopsis

SHOW CREATE object_name

Parameters

Parameter Description
object_name The name of the table, view, or sequence for which to show the CREATE statement.

Response

Field Description
table_name The name of the table, view, or sequence.
create_statement The CREATE statement for the table, view, or sequence.

Example

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

copy
icon/buttons/copy
$ cockroach demo

Show the CREATE TABLE statement for a table

copy
icon/buttons/copy
> CREATE TABLE drivers (
    id UUID NOT NULL,
    city STRING NOT NULL,
    name STRING,
    dl STRING UNIQUE,
    address STRING,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);
copy
icon/buttons/copy
> SHOW CREATE TABLE drivers;
  table_name |                     create_statement
-------------+-----------------------------------------------------------
  drivers    | CREATE TABLE public.drivers (
             |     id UUID NOT NULL,
             |     city STRING NOT NULL,
             |     name STRING NULL,
             |     dl STRING NULL,
             |     address STRING NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     UNIQUE INDEX drivers_dl_key (dl ASC),
             |     FAMILY "primary" (id, city, name, dl, address)
             | )
(1 row)

To return just the create_statement value:

copy
icon/buttons/copy
> SELECT create_statement FROM [SHOW CREATE TABLE drivers];
                      create_statement
------------------------------------------------------------
  CREATE TABLE public.drivers (
      id UUID NOT NULL,
      city STRING NOT NULL,
      name STRING NULL,
      dl STRING NULL,
      address STRING NULL,
      CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
      UNIQUE INDEX drivers_dl_key (dl ASC),
      FAMILY "primary" (id, city, name, dl, address)
  )
(1 row)
Note:

SHOW CREATE TABLE also lists any partitions and zone configurations defined on primary and secondary indexes of a table. If partitions are defined, but no zones are configured, the SHOW CREATE TABLE output includes a warning.

Show the CREATE VIEW statement for a view

copy
icon/buttons/copy
> CREATE VIEW user_view (city, name) AS SELECT city, name FROM users;
copy
icon/buttons/copy
> SHOW CREATE user_view;
  table_name |                                   create_statement
-------------+----------------------------------------------------------------------------------------
  user_view  | CREATE VIEW public.user_view (city, name) AS SELECT city, name FROM movr.public.users
(1 row)

To return just the create_statement value:

copy
icon/buttons/copy
> SELECT create_statement FROM [SHOW CREATE VIEW user_view];
                                    create_statement
-----------------------------------------------------------------------------------------
  CREATE VIEW public.user_view (city, name) AS SELECT city, name FROM movr.public.users
(1 row)

Show just a view's SELECT statement

To get just a view's SELECT statement, you can query the views table in the built-in information_schema database and filter on the view name:

copy
icon/buttons/copy
> SELECT view_definition
  FROM information_schema.views
  WHERE table_name = 'user_view';
              view_definition
--------------------------------------------
  SELECT city, name FROM movr.public.users
(1 row)

Show the CREATE SEQUENCE statement for a sequence

copy
icon/buttons/copy
> CREATE SEQUENCE desc_customer_list START -1 INCREMENT -2;
copy
icon/buttons/copy
> SHOW CREATE desc_customer_list;
      table_name     |                                             create_statement
---------------------+------------------------------------------------------------------------------------------------------------
  desc_customer_list | CREATE SEQUENCE public.desc_customer_list MINVALUE -9223372036854775808 MAXVALUE -1 INCREMENT -2 START -1
(1 row)

To return just the create_statement value:

copy
icon/buttons/copy
> SELECT create_statement FROM [SHOW CREATE desc_customer_list];
                                              create_statement
-------------------------------------------------------------------------------------------------------------
  CREATE SEQUENCE public.desc_customer_list MINVALUE -9223372036854775808 MAXVALUE -1 INCREMENT -2 START -1
(1 row)

Show the CREATE TABLE statement for a table with a comment

If you add a comment on a table, SHOW CREATE TABLE will display the comment.

copy
icon/buttons/copy
> COMMENT ON TABLE users IS 'This table contains information about users.';
copy
icon/buttons/copy
> SHOW CREATE TABLE users;
  table_name |                                create_statement
-------------+----------------------------------------------------------------------------------
  users      | 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)
             | );
             | COMMENT ON TABLE public.users IS 'This table contains information about users.'
(1 row)

To return just the create_statement value:

copy
icon/buttons/copy
> SELECT create_statement FROM [SHOW CREATE TABLE users];
                                 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)
  );
  COMMENT ON TABLE public.users IS 'This table contains information about users.'
(1 row)

For more information, see COMMENT ON.

See also



Yes No