My friend Dave recently told me about the "CREATE TABLE ... LIKE
" SQL statement. MySql and DB2 both
support it, and it's apparently part of the SQL99 spec. It's not supported by
T-SQL (SQL Server).
CREATE TABLE LIKE is quick way
of cloning the structure of an existing table, including any indexes (but not
foreign keys). This can be particularly useful in creating new tables to be
added to an existing MERGE table.
mysql> CREATE
TABLE log_20041124 LIKE log_20041123;
As far as I know, this will not be supported by T-SQL, despite
all the other SQL enhancements in Yukon. You can copy everything except the keys /
indexes with "SELECT INTO":
select * into CustomersCopy from
Customers where 1 = 0
That copies the
structure, but doesn't copy the indexes.
One other trick I learned a bit ago: you can select a table in Enterprise
Manager and hit control - c. Then go to notepad / query analyzer / etc. and
paste to get the create table statement (with all the indexes and stuff). You
can also right click a table in Query Analyzer and select "Script Object To New
Window As..." to get a Create statement. Still, none of these offers quite what
the CREATE TABLE ... LIKE
statement does - script level copy of table scructure and
indexes.