How to get query that would recreate sql table in PHPMyAdmin

Question

I have table on MySQL server and would like to get the SQL that would re-create the table.

How do I get the query to recreate the SQL table?

Scornik

From the commandline: mysqldump -u yourlogin -p your_database your_tablename. Enter password and then the create table statement will be shown. Of if you want to get it from the phpmyadmin GUI, run the query: show create table your_tablename – Acrimonious

Possible duplicate of How to take backup of a single table in a MySQL database? – Eucaine

Answer 1

mysqldump can do it - http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

You can use it like this:

mysqldump [databasename] [tablename] > mysqltablesql.sql

Amass

Answer 2

MySQL supports SHOW CREATE TABLE to return the SQL that was used to create a table.

From their docs:

mysql> SHOW CREATE TABLE t;
CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM

This can be useful if you have just a connection to the DB, versus a CLI on the server. If you have a CLI, use mysqldump like liamgriffiths recommends.

Maus

This does not deal with copying rows which I think was part of the expected result. – Countershaft

Answer 3

If you are using phpMyAdmin, select a table then click the 'Export' tab. One of the output types is SQL. Selecting this will generate a SQL script to create the table and insert all of the data that is currently in the table. Make sure under the dump data option to select 'Structure and Data'

Schoolgirl

Thanks, this is the best answer for me. An sql file is just a list of instructions to build a database, right at the top is the query for the table structure. – Penis

Answer 4

If you have access to phpMyAdmin, you can get this code through the Export tab on the table that you require.

Select SQL then make sure you export "Structure" ("CREATE table" code) and "Data" ("INSERT" code) with Export type set to "INSERT".

Coppola

Answer 5

mysqldump [OPTIONS] database [tables]>backup-file.sql

If you don't give any tables or use the --databases or --all-databases, the whole database(s) will be dumped.

You can get a list of the options your version of mysqldump supports by executing mysqldump --help.

Note that if you run mysqldump without --quick or --opt, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database.

Fiddlefaddle

Answer 6

I'd go with @liamgriffiths proposal of mysqldump, but you could also try create table <new-table-name> like <old-table-name> followed by insert into <new-table-name> select * from <old-table-name>

Yentai