How to get a table creation script in MySQL Workbench?

Question

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.

Answer 1

Not sure if I fully understood your problem, but if it's just about creating export scripts, you should forward engineer to SQL script - Ctrl + Shift + G or File -> Export -> first option.

Answer 2

show create table

Answer 3

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 :-(

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.

Answer 4

Right-click on the relevant table and choose either of:

• Copy to Clipboard > Create Statement
• Send to SQL Editor > Create Statement

That seems to work for me.