Show Create Table Script in MySQL

This is an article written simply to show how to display a script for creating a table in MySQL Database Server. The table itself has already been created and the script itself is useful for recreating the table if there is really a purpose to do that in other occasion or simply try to assess and to look the component of the table in details. For an example to look at the component of the table whether it contains a specific foreign key column which refers to another column exist in another table.

To achieve the purpose in the context of this article, it can simply be done using the help of MySQL GUI Editor such as MySQL Workbench, Navicat or any multipurpose SQL GUI Editor available in the market. But for this article context, it is going to be presented in the form of text-based query executed in MySQL Command Console. Below are steps needed to be taken to aim for the goal :

1. Check MySQL Server Service. To be more specific, it can be read in the article titled ‘Check MySQL Service Status’ and it is available in this link.

2. Login to MySQL Command Console. As further reference, it can be seen in the article titled ‘MySQL Database Access from Command Line in Linux’ in this link. Another article which can be used as another reference is available in an article titled ‘Remote Execute MySQL Query via Command Line’ and it is available in this link.

3. Execute the query which is intended to show the generated script for creating the table associated. Below is the pattern of the query :

show create table table_name

For an example :

mysql> show create table posts;
+-------+---------------------------------------------------------+
| Table | Create Table                                            |
+-------+---------------------------------------------------------+
| posts | CREATE TABLE `posts` (
  `id_post` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `id_category` int(11) NOT NULL,
  `id_subcategory` int(11) NOT NULL,
  `id_maincategory` int(11) NOT NULL,
  PRIMARY KEY (`id_post`),
  KEY `id_subcategory` (`id_subcategory`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`id_subcategory`) 
REFERENCES `subcategory` (`id_subcategory`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1          |
+-------+---------------------------------------------------------+
1 row in set (0,04 sec)

mysql>

The above output generated can be useful for creating a new table with the similar structure with the already existed table named ‘posts’.

1 thought on “Show Create Table Script in MySQL”

Leave a Reply