How do you copy the structure of a table?

[moondog]

How do you copy the table structure from one table to another via an SQL statement so all of the table attributes (like primary keys, what can be null, default values, enum values, etc) remain in tact?

I know you can do it via phpMyAdmin in the "operations" tab, but I need to do this via a script.

Anybody know?

-Bob

 

[Amnezia]

use this command to create a complete copy of your source table

CREATE TABLE dest_table AS SELECT * FROM source_table;

then to clear the destination table whilst keeping the stucture in place, use the following command

TRUNCATE dest_table;

 

[moondog]

use this command to create a complete copy of your source table

CREATE TABLE dest_table AS SELECT * FROM source_table;

then to clear the destination table whilst keeping the stucture in place, use the following command

TRUNCATE dest_table;

Well, this almost works.

The table is copied, however the primary key information is not ported over.

The primary key in the source table is

id

and is tagged as such, plus it has auto_increment set. In the destination table, neither of these two atriubutes are set.



From here I supposed I can use:

ALTER TABLE <table name> ADD PRIMARY KEY(<field>);

and then

ALTER TABLE <table name> CHANGE <field> <new field name> <data type> <null / not null> <default> AUTO_INCREMENT


but it seems like the primary key + auto_increment should be preserved in the copy-over. . ..

-Bob

 

[superprogrammer]

CREATE TABLE dest_table AS SELECT * FROM source_table where 1=2;
No need of truncate

For constraints you have to check in the data dictionary with the same table name and put it over the new table