自动生成表结构 sql 从已知数据库获得创建表的结构 sql server

使用这个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