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.

When I Use the Script for a Table in MySQL

Getting the CREATE statement with plain SQL is handy in more places than you may think. I use it when I need the same table on a test server, when I want to keep the structure in source control, and when two environments behave differently and I want to compare the table definitions side by side.

A few things to keep in mind. The output includes table options such as the storage engine and the default character set, so read it before you run it somewhere else. If the table already has rows, it can also show the current AUTO_INCREMENT value, which you may not want on a fresh copy. In the mysql command line client, end the statement with \G instead of a semicolon to see the long output in a readable vertical layout.

If you only want an empty copy on the same server, CREATE TABLE new_table LIKE old_table is even quicker. It copies the columns and indexes, but not the foreign keys. For many tables at once, mysqldump with the --no-data option writes the structure of every table to one file.

And on the SQL Server side, the easiest route is still SSMS: right click the table, choose Script Table as, then CREATE To.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.