Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get a table creation script in MySQL Workbench?

I am rolling back to MySQL GUI Tools' MySQL Query Browser since I can't find the shortcut to get a table's creation script in MySQL Workbench.

like image 312
Jader Dias Avatar asked Mar 12 '10 13:03

Jader Dias


People also ask

How do you get the create script of a table in MySQL?

In MySQL workbench, you get the already created table script, by just right click on the specific table, then select send to SQL editor>>create statement . That's all you will get the create table script on the editor.


2 Answers

I cannot find such an option either, at least in the Community edition.

I suppose this corresponds to the Reverse Engineering feature, which, unfortunately, is only available in the commercial edition (quoting) :

reverse engineering a database directly from a MySQL server applies to commercial versions of MySQL Workbench only.


Still, you can use plain-SQL to get the create table instruction that will allow you to create a table.

For instance, the following query :

show create table url_alias; 

when executed on a drupal database, would give, when using right click > copy field content on the result :

'CREATE TABLE `url_alias` (   `pid` int(10) unsigned NOT NULL auto_increment,   `src` varchar(128) NOT NULL default '''',   `dst` varchar(128) NOT NULL default '''',   `language` varchar(12) NOT NULL default '''',   PRIMARY KEY  (`pid`),   UNIQUE KEY `dst_language` (`dst`,`language`),   KEY `src_language` (`src`,`language`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8' 

Unfortunately (again), MySQL Workbench adds some quotes everywhere when copying this way :-(


EDIT: Using MySQL 8.0, there is an option to right click > copy field (unquoted) on the result to get the desired result without quotes.


In the end, the simplest solution, except from staying with MySQL Query Browser, will most likely be to connect to the database, using the command-line client, and execute the show create table query from there :

mysql> show create table url_alias\G *************************** 1. row ***************************        Table: url_alias Create Table: CREATE TABLE `url_alias` (   `pid` int(10) unsigned NOT NULL auto_increment,   `src` varchar(128) NOT NULL default '',   `dst` varchar(128) NOT NULL default '',   `language` varchar(12) NOT NULL default '',   PRIMARY KEY  (`pid`),   UNIQUE KEY `dst_language` (`dst`,`language`),   KEY `src_language` (`src`,`language`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 

Getting "the right portion" of the output is easier, there : no quote to remove.



And, just for the sake of completness, you could also use mysqldump to get your table's structure :

mysqldump --no-data --user=USERNAME --password=PASSWORD --host=HOST DATABASE_NAME TABLE_NAME 

Using the --no-data switch, you'll only get the structure -- in the middle of some mode settings and all that.

like image 102
Pascal MARTIN Avatar answered Sep 21 '22 13:09

Pascal MARTIN


To get an individual table's creation script just right click on the table name and click Copy to Clipboard > Create Statement.

To enable the File > Forward Engineering SQL_CREATE Script.. option and to get the creation script for your entire database :

  1. Database > Reverse Engineer (Ctrl+R)
  2. Go through the steps to create the EER Diagram
  3. When viewing the EER Diagram click File > Forward Engineering SQL_CREATE Script... (Ctrl+Shift+G)
like image 26
Phil McCullick Avatar answered Sep 22 '22 13:09

Phil McCullick