'How to generate a create table script for an existing table in phpmyadmin?
How can I generate a create table script for an existing table in phpmyadmin?
Solution 1:[1]
Run SHOW CREATE TABLE <table name> query.
Solution 2:[2]
Mysqladmin can do the job of saving out the create table script.
Step 1, create a table, insert some rows:
create table penguins (id int primary key, myval varchar(50))
insert into penguins values(2, 'werrhhrrhrh')
insert into penguins values(25, 'weeehehehehe')
select * from penguins
Step 2, use mysql dump command:
mysqldump --no-data --skip-comments --host=your_database_hostname_or_ip.com -u your_username --password=your_password your_database_name penguins > penguins.sql
Step 3, observe the output in penguins.sql:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `penguins`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `penguins` (
`id` int(11) NOT NULL,
`myval` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
The output is cluttered by a number of executional-condition tokens above and below. You can filter them out if you don't want them in the next step.
Step 4 (Optional), filter out those extra executional-condition tokens this way:
mysqldump --no-data --skip-comments --compact --host=your_database_hostname_or_ip.com -u your_username --password=your_password your_database_name penguins > penguins.sql
Which produces final output:
eric@dev /home/el $ cat penguins.sql
DROP TABLE IF EXISTS `penguins`;
CREATE TABLE `penguins` (
`id` int(11) NOT NULL,
`myval` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Solution 3:[3]
Run query is sql tab
SHOW CREATE TABLE tableName
Click on
+Options -> Choose Full texts -> Click on Go
Copy Create Table query and paste where you want to create new table.
Solution 4:[4]
Query information_schema.columns directly:
select * from information_schema.columns
where table_name = 'your_table' and table_schema = 'your_database'
Solution 5:[5]
This may be a late reply. But it may help others. It is very simple in MY SQL Workbench ( I am using Workbench version 6.3 and My SQL Version 5.1 Community edition): Right click on the table for which you want the create script, select 'Copy to Clipboard --> Create Statement' option. Simply paste in any text editor you want to get the create script.
Solution 6:[6]
Use the following query in sql tab:
SHOW CREATE TABLE your_table_name
Press GO button
After show table, above the table ( +options ) Hyperlink.
Press +options Hyperlink then appear some options select (Full texts) press GO button.
Show sql quaery.
Solution 7:[7]
Using PHP Function.
Of course query function ($this->model) you have to change to your own.
/**
* Creating a copy table based on the current one
*
* @param type $table_to_copy
* @param type $new_table_name
* @return type
* @throws Exception
*/
public function create($table_to_copy, $new_table_name)
{
$sql = "SHOW CREATE TABLE ".$table_to_copy;
$res = $this->model->queryRow($sql, PDO::FETCH_ASSOC);
if(!filled($res['Create Table']))
throw new Exception('Could not get the create code for '.$table_to_copy);
$newCreateSql = preg_replace(array(
'@CREATE TABLE `'.$table_to_copy.'`@',
'@KEY `'.$table_to_copy.'(.*?)`@',
'@CONSTRAINT `'.$table_to_copy.'(.*?)`@',
'@AUTO_INCREMENT=(.*?) @',
), array(
'CREATE TABLE `'.$new_table_name.'`',
'KEY `'.$new_table_name.'$1`',
'CONSTRAINT `'.$new_table_name.'$1`',
'AUTO_INCREMENT=1 ',
), $res['Create Table']);
return $this->model->exec($newCreateSql);
}
Solution 8:[8]
- SHOW CREATE TABLE your_table_name => Press GO button
After show table, above the table ( +options ) Hyperlink is there.
- Press (+options) Hyperlink then appear some options select (Full texts) => Press GO button.
Solution 9:[9]
- Select the Database required
- Select the tables you want to SHOW CREATE TABLE
- Click on dropdown With Selected: and select Show create
Solution 10:[10]
I found another way to export table in sql file.
Suppose my table is abs_item_variations
abs_item_variations ->structure -> propose table structure -> export -> Go
Solution 11:[11]
Export whole database select format as SQL. Now, open that SQL file which you have downloaded using notepad, notepad++ or any editor. You will see all the tables and insert queries of your database. All scripts will be available there.
Solution 12:[12]
Right click on table name-->choose open table --> Go to Info Tab
and the scroll down to see create table script
Solution 13:[13]
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.
Solution 14:[14]
One more way. Select the target table in the left panel in phpMyAdmin, click on Export tab, unselect Data block and click on Go button.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow