SQL2008 - Create Table Based on User-Defined Table Type

Table types are really neat. Mind you, one of the features I would love to see is the ability to create a table based on a table type (i.e. CREATE TABLE LIKE ).

Imagine this scenario, you define all your table types up front with extended property "TableType" i.e. (each Table Type will have whatever columns it needs - but will have extended property "TableType")

"TableType" 1 = Lookup table,

"TableType" 2 = Lookup table,

"TableType" 3 = Fact table,

"TableType" 4 = Dimension table

(Assuming a data warehouse here.)

... OR ...
Table Type 1 = Lookup table,

Table Type 2 = Data table,

Table Type 3 = History table

(Assuming an OLTP system)

You would be able to define the table types in one location, and the extended properties and indexes (optionally) could be propagated to the tables you create based on those types. I picture it almost like inheritance, you could create a table based on a table type - then extend the table type to include additional properties.

Advantages of this would be that you could apply manageability rules (similar to what VS2005 has and standard sets of unit tests to your tables that are created based on such types). Until such a time as this becomes reality, here are some workarounds. (Of course, you will have to do some work to propagate extended properties from tables/table types that you inherit from.)

1. Create Table based on another table


SELECT top 0 * INTO mynewtable from mytable

2. Create Temporary Table based on another table


SELECT top 0 * INTO #mynewtable from mytable

3. Create Table based on table type


DECLARE @mytable MyTableType
SELECT TOP 0 * INTO mynewtable FROM @mytable