How to get a table creation script in MySQL Workbench?
MysqlMysql WorkbenchMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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 :-(
click > copy field (unquoted)
on the result to get the desired result without quotes.
EDIT: Using MySQL 8.0, there is an option to right
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.
Solution 2 - Mysql
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 :
- Database > Reverse Engineer (Ctrl+R)
- Go through the steps to create the EER Diagram
- When viewing the EER Diagram click File > Forward Engineering SQL_CREATE Script... (Ctrl+Shift+G)
Solution 3 - Mysql
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.
Solution 4 - Mysql
It is located in server administration rather than in SQL development.
- From the home screen select the database server instance your database is located on from the server administration section on the far right.
- From the menu on the right select Data Export.
- Select the database you want to export and choose a location.
- Click start export.
Solution 5 - Mysql
Simply use:
show create table <table_name>
Solution 6 - Mysql
I came here looking for the answer to the same question. But I found a much better answer myself.
In the tables list, if you right-click on the table name there is a suite of CRUD script generation options in "Send to SQL Editor". You can select multiple tables and take the same approach too.
My version of MySQL Workbench: 5.2.37
Solution 7 - Mysql
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.
Solution 8 - Mysql
1 use command
show create table test.location
Solution 9 - Mysql
Solution for MySQL Workbench 6.3E
- On left panel, right click your table and selecct "Table Inspector"
- On center panel, click DDL label
Solution 10 - Mysql
In "model overview" or "diagram" just right-click on the table and you have the folowing options: "Copy Insert to clipboard" OR "Copy SQL to clipboard"
Solution 11 - Mysql
Not sure if this is still an issue, but for me in 5.2.35CE it's possible to get the create scripts by:
-
Database --> Reverse Engineer
-
Under stored connection, choose your database
-
Hit "Next" a few times, choose which schema you want to reverse engineer, and let the tool work
-
You'll get an "EER Diagram" view with all the DB's schema. If you right click on the table you care about and choose "Copy SQL to Clipboard" I think you'll have what you need.
Hopefully this helps someone else that needs it.
Solution 12 - Mysql
- Open MySQL Workbench (6.3 CE)
- In "Navigator" select "Management"
- Then select "Data Export" (Here select the table whose create script you wish to export)
- In Drop down select "Dump Structure and Data"
- Select checkbox "Include Create Schema"
- Click the button "Start Export" Once export is complete it will display the location in which exported file is dumped in your system. Go to the location and open the exported file to find table creation script.
Or Check https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html
Solution 13 - Mysql
U can use MySQL Proxy and its scripting system to view SQL queries in realtime in the terminal.