We can use MariaDB’s SHOW CREATE TABLE statement to generate a CREATE TABLE script for existing tables in the database. This allows us to recreate the table without having to manually type out the table’s definition.

The statement requires SELECT privilege for the table.

Example

Here’s an example to demonstrate:

SHOW CREATE TABLE Pets;

Result:

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pets  | CREATE TABLE `pets` (
  `PetId` int DEFAULT NULL,
  `PetName` varchar(50) DEFAULT NULL,
  `PetType` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The SHOW CREATE TABLE statement also works with views and SEQUENCE.