# SHOW CREATE

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

## 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:

    $ cockroach demo

### Show the `CREATE TABLE` statement for a table

    > 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)
    );

    > SHOW CREATE TABLE drivers;

      table_name |                     create_statement
    +------------+----------------------------------------------------------+
      drivers    | CREATE TABLE 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:

    > SELECT create_statement FROM [SHOW CREATE TABLE drivers];

                          create_statement
    ------------------------------------------------------------
      CREATE TABLE 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

    > CREATE VIEW user_view (city, name) AS SELECT city, name FROM users;

    > SHOW CREATE user_view;

      table_name |                                create_statement
    +------------+--------------------------------------------------------------------------------+
      user_view  | CREATE VIEW user_view (city, name) AS SELECT city, name FROM movr.public.users
    (1 row)

To return just the `create_statement` value:

    > SELECT create_statement FROM [SHOW CREATE VIEW user_view];

                                     create_statement
    ----------------------------------------------------------------------------------
      CREATE VIEW 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:

    > 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

    > CREATE SEQUENCE desc_customer_list START -1 INCREMENT -2;

    > SHOW CREATE desc_customer_list;

          table_name     |                                          create_statement
    +--------------------+----------------------------------------------------------------------------------------------------+
      desc_customer_list | CREATE SEQUENCE desc_customer_list MINVALUE -9223372036854775808 MAXVALUE -1 INCREMENT -2 START -1
    (1 row)

To return just the `create_statement` value:

    > SELECT create_statement FROM [SHOW CREATE desc_customer_list];

                                               create_statement
    ------------------------------------------------------------------------------------------------------
      CREATE SEQUENCE 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.

    > COMMENT ON TABLE users IS 'This table contains information about users.';

    > SHOW CREATE TABLE users;

      table_name |                             create_statement
    -------------+---------------------------------------------------------------------------
      users      | CREATE TABLE 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 users IS 'This table contains information about users.'
    (1 row)

To return just the `create_statement` value:

    > SELECT create_statement FROM [SHOW CREATE TABLE users];

                                  create_statement
    ----------------------------------------------------------------------------
      CREATE TABLE 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 users IS 'This table contains information about users.'
    (1 row)

For more information, see `COMMENT ON`.

## See also

- `CREATE TABLE`
- `CREATE VIEW`
- `CREATE TABLE`
- Information Schema
- Other SQL Statements