To quickly replica the schema of a table in business development, we recommend that you use the SHOW CREATE TABLE syntax. Compared with the DESCRIBE syntax, the SHOW CREATE TABLE can return the complete statement used to create the table. You can directly execute the returned statement to create a new table with the same schema.
Applicable engines and versions
The SHOW CREATE TABLE syntax is applicable only to LindormTable.
The SHOW CREATE TABLE syntax is supported by LindormTable 2.6.2 and later versions.
ImportantTo use the SHOW CREATE TABLE syntax, the version of Lindorm SQL must be later than 2.6.3.2. For more information about how to view the Lindorm SQL version, see View Lindorm SQL versions.
Syntax
show_create_table_statement ::= SHOW CREATE TABLE table_identiferThe SHOW CREATE TABLE syntax does not return the table attributes that are configured when the table is created. You can log on to the cluster management system to view the values of the parameters corresponding to the attributes. For more information about table attributes, see Table attributes (table_options).
The results returned by the SHOW CREATE TABLE syntax do not include the dynamic columns added to the table.
Examples
In this example, a table named sensor is created by using the following statements:
-- Create a table named sensor.
CREATE TABLE sensor (p1 INT, c1 INT, c2 VARCHAR, PRIMARY KEY(p1)) WITH (COMPRESSION='ZSTD', TTL='2592000', DYNAMIC_COLUMNS='TRUE');
-- Add a dynamic column named c3 to the table.
UPSERT INTO sensor (p1, c2, c3) VALUES (1, '1', '41');Execute the following statement to query the statement used to create the sensor table:
SHOW CREATE TABLE sensor;The following result is returned:
+--------+--------------------------------+
| TABLE | CREATE TABLE |
+--------+--------------------------------+
| sensor | CREATE TABLE sensor(p1 int |
| | NOT NULL, c1 int, c2 varchar, |
| | PRIMARY KEY (p1)) |
+--------+--------------------------------+