MySQL – Generate Script for a Table Using SQL

In SQL Server, to generate the CREATE TABLE script for a table, you need to rely on the SQL Server Management Studio (SSMS) tool and there is no inbuilt function supported to do this using SQL. However, in MySQL you can generate the script for a table using SQL.

Let us create the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));


Now to view the script of the table sales, you can make use of SHOW CREATE TABLE statement. This statement accepts table name as parameter and returns the CREATE TABLE script for that table.

Run the following code

SHOW CREATE TABLE sales;

The resultset has two columns where the second column displays the following script.

CREATE TABLE 'sales' (
'sales_id' INT(11) NOT NULL AUTO_INCREMENT,
'item_id' INT(11) DEFAULT NULL,
'sales_date' DATETIME DEFAULT NULL,
'sales_amount' DECIMAL(12,2) DEFAULT NULL,
PRIMARY KEY ('sales_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Note : You can use the same SHOW CREATE TABLE statement to view the script for a VIEW although there is a seperate SHOW CREATE VIEW statement that accepts view name as a parameter.

Reference: Pinal Dave (https://blog.sqlauthority.com)