使用这个sql可以在sql sever中生成已经存数据中创建表的表结构,还可以加上主键,外键,这里先不写,下次更新
-----------------------------------------------------------------------------------------------------
select 'create table '+tablename+'('+stuff(cast(cols as varchar(8000)),1,1,'') +');'
from (
select tablename,(
select ','+cc.type from (
select tablename,colname+' '+typename + (case when collen <> '' then '('+collen+')' else '' end) as type
from(
select tb.name as tablename,col.name colname,
tp.name as typename
,case
when tp.name='numeric' or tp.name='float' or tp.name='real' or tp.name='decimal' then cast(col.precision as varchar(10))+','+cast(col.scale as varchar(10))
when tp.name='int' then cast(col.precision as varchar(10))+''
when tp.name='timestamp' or tp.name='datetime' then ''
else cast(col.max_length as varchar(10))+''
end as collen
from sys.columns col
left join sys.types tp on col.user_type_id=tp.user_type_id
left join sys.tables tb on tb.object_id=col.object_id
where type='U'
) as t
) cc where cc.tablename=aa.tablename for xml path('')
) as cols
from (
select tablename,colname+' '+typename + (case when collen <> '' then '('+collen+')' else '' end) as type
from(
select tb.name as tablename,col.name colname,
tp.name as typename
,case
when tp.name='numeric' or tp.name='float' or tp.name='real' or tp.name='decimal' then cast(col.precision as varchar(10))+','+cast(col.scale as varchar(10))
when tp.name='int' then cast(col.precision as varchar(10))+''
when tp.name='timestamp' or tp.name='datetime' then ''
else cast(col.max_length as varchar(10))+''
end as collen
from sys.columns col
left join sys.types tp on col.user_type_id=tp.user_type_id
left join sys.tables tb on tb.object_id=col.object_id
where type='U'
) as t
) aa
group by tablename
) dd