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 (http://blog.sqlauthority.com)

About these ads

2 thoughts on “MySQL – Generate Script for a Table Using SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s