[wish] "CREATE TABLE ... LIKE" in SQL Server 2005

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.


Comments have been disabled for this content.