SHOW CREATE

On this page Carat arrow pointing down

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

Show the CREATE TABLE statement for a table

icon/buttons/copy
> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);
icon/buttons/copy
> SHOW CREATE customers;
+------------+---------------------------------------------------+
| table_name |                 create_statement                  |
+------------+---------------------------------------------------+
| customers  | CREATE TABLE customers (                          |
|            |                                                   |
|            |     id INT NOT NULL,                              |
|            |                                                   |
|            |     email STRING NULL,                            |
|            |                                                   |
|            |     CONSTRAINT "primary" PRIMARY KEY (id ASC),    |
|            |                                                   |
|            |     UNIQUE INDEX customers_email_key (email ASC), |
|            |                                                   |
|            |     FAMILY "primary" (id, email)                  |
|            |                                                   |
|            | )                                                 |
+------------+---------------------------------------------------+
(1 row)

Show the CREATE VIEW statement for a view

icon/buttons/copy
> SHOW CREATE bank.user_accounts;
+---------------------------+--------------------------------------------------------------------------+
|        table_name         |                             create_statement                             |
+---------------------------+--------------------------------------------------------------------------+
| bank.public.user_accounts | CREATE VIEW user_accounts (type, email) AS SELECT type, email FROM       |
|                           | bank.public.accounts                                                     |
+---------------------------+--------------------------------------------------------------------------+
(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:

icon/buttons/copy
> SELECT view_definition
  FROM information_schema.views
  WHERE table_name = 'user_accounts';
+----------------------------------------------+
|               view_definition                |
+----------------------------------------------+
| SELECT type, email FROM bank.public.accounts |
+----------------------------------------------+
(1 row)

Show the CREATE SEQUENCE statement for a sequence

icon/buttons/copy
> CREATE SEQUENCE desc_customer_list START -1 INCREMENT -2;
icon/buttons/copy
> 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)

See also


Yes No
On this page

Yes No