How to generate a create table script for an existing table in phpmyadmin?

Question

How can I generate a create table script for an existing table in phpmyadmin?

Answer 1

Use one of the following queries in sql tab:

SHOW CREATE TABLE tablename;
SHOW CREATE TABLE database.tablename;

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

Answer 2

Run SHOW CREATE TABLE <table name>; query.

Answer 3

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;

Answer 4

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.

Answer 5

You can query information_schema.columns directly:

SELECT * FROM information_schema.columns 
WHERE table_name = 'your_table' AND table_schema = 'your_database'

Answer 6

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.

Answer 7

1. Use the following query in sql tab: SHOW CREATE TABLE your_table_name;
2. Press GO button
3. After show table, above the table ( +options ) Hyperlink.
4. Press +options Hyperlink then appear some options select (Full texts) press GO button.
5. Show sql query.

Answer 8

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

Answer 9

1. Right click on table name
2. Choose open table
3. Go to Info Tab

and the scroll down to see create table script

Answer 10

1. SHOW CREATE TABLE your_table_name

2. Press GO button

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

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

Answer 11

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

Answer 12

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

Answer 13

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.

Answer 14

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.

Answer 15

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.