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?

Use the following query in sql tab:

SHOW CREATE TABLE tablename

To view full query There is this Hyperlink named +Options left above, There select Full Texts

Run SHOW CREATE TABLE <table name> query.

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;

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.

Query information_schema.columns directly:

select * from information_schema.columns 
where table_name = 'your_table' and table_schema = 'your_database'

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.

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.

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);
}

1. SHOW CREATE TABLE your_table_name => Press GO button

After show table, above the table ( +options ) Hyperlink is there.

2. Press (+options) Hyperlink then appear some options select (Full texts) => Press GO button.

1. Select the Database required
2. Select the tables you want to SHOW CREATE TABLE
3. Click on dropdown With Selected: and select Show create

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

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.

Right click on table name-->choose open table --> Go to Info Tab

and the scroll down to see create table script

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.