copy table structure 1

[dky1e]

Hi All,

I need to copy table structures from one database to another. The copies will contain additional fields. Only table names, column names and column data types must be copied (no indexes, fk, pk, etc...).

I need to build the 'create table ...' statement and then execute it on another db.

My issue is building correct datatypes for each column, in more detail I am not sure when to specify the size of fields such as varchar(100), decimal(8,2) and when not to such as int, text, ntext, etc...

Another issue is custom user data types...

I've looked at INFORMATION_SCHEMA.COLUMNS view, but I can't find a best way of retrieving this data.

Any hints/tips?

 

[ecobb]

This would copy the structure only, no data, indexes PK, triggers, etc... Assuming that the databases are on the same server.

use NewDatabase
go

Select * into NewTableName
From OldDatabaseName.dbldTableName
Where 0 = 1

Hope This Helps!

Ecobb
- I hate computers!

 

[dky1e]

wow.

Thanks.

 

[Gauss]

... or:


Select top 0 * into NewTableName
From OldDatabaseName.dbldTableName


Gauss

 

[dky1e]

err... i got exited too quickly.

This also copies identity property of columns, any way to avoid this?

 

[dky1e]

I would like to get rid off identity columns or I can do identity_insert on when inserting to these tables.

Is there an easy way to find out whether the table contains an identity column?

 

[royjimenez]

u can do something like this:

select field1, field2, field3, 0 fextra, ' ' fextra2
into table
from db.user.table2
where 1 = 0

how u can see, u are setting the fields and adding fextra and fextra2.

If u want to know the ID columns with u tables:

select * from sysobjects so, syscolumns sc
where so.id = sc.id and
so.xtype = 'U' and
sc.autoval is not null
order by 1

regards