How to copy table structure only 4

[dalchri]

Hi,

I was wondering if there was a way using SQL statements to copy the database structure of one table into another empty table. I saw a tantalizing CREATE TABLE table_name LIKE old_table_name SQL statement in the mysql manual but it doesn't work on our linux server.

Using CREATE TABLE table_name SELECT * FROM old_table_name does not properly copy the structure of the table either. I was hoping to pull this off with a SQL statement because I have to work through an ADO connection. I will not know the physical location of the database to do file copying operations.

Thank you for any help!

 

[sleipnir214]

Failing all else, you could issue the statement &quot;DESCRIBE <tablename>&quot; against the old database, programmatically process that data into a second SQL statement, and issue the SQL statement against the new database. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[dalchri]

That rocks. Thanks for the info! It got me on the path to SHOW CREATE TABLE <tablename>.

 

[sleipnir214]

Thank you. You've showed me something about MySQL I didn't know, either.

A Star for dalchri! Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[PCSAARON]

You can also use the mysqldump command.

mysqldump -u root -p -d DatabaseName TableName >file.sql

(-d means no data, just structure)

Then use:

mysql -u root -p DatabaseName <file.sql

I like using mysqldump, but it is your preference...

Aaron

 

[cyoung]

Thanks for the tips on this. I also had trouble copying the structure from 1 table to another, but the following SQL statement works...

CREATE TABLE new_table_name SELECT * FROM old_table_name WHERE 0;

This will create a new table with the same structure, but will not copy any records.

 

[tshot]

thanks dalchri for showing us a nundocumented feature. one star from me also




----------------
ur feedback is a very welcome desire

 

[MartijnTonies]

Actually, SHOW CREATE TABLE _is_ documented

http://www.mysql.com/doc/en/SHOW_CREATE_TABLE.html

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[dalchri]

I actually got it off of the MySQL-Front UI for the MySQL database server. MySQL-Front is a GUI but it actually lists the SQL statments that it is issuing to produce its GUI presentation. Its a treasure trove for the reverse engineer!

 

[MartijnTonies]

SHOW CR TAB can be nice indeed. In Database Workbench, I'm using it to parse FKs for InnoDB tables. It's the only way to get info about the FKs, no other info stored. Anywhere. :-/

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com