sqlserver 查看建表语句，生成建表语句 sqlserver show create table

sqlserver 没有类似 mysql的

show create table 语句

项目需求每天备份数据和建表sql，sqlserver虽然自带查看建表语句功能，但是也不能每天上去手动点点点保存啊。

只能拼装出来
直接上sql

select 'create table [' + so.name + '] (' + o.list + ')' + CASE
                                                                       WHEN tc.Constraint_Name IS NULL THEN ''
                                                                       ELSE 'ALTER TABLE ' + so.Name +
                                                                            ' ADD CONSTRAINT ' + tc.Constraint_Name +
                                                                            ' PRIMARY KEY ' +
                                                                            ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
        from sysobjects so
            cross apply
            (SELECT
            '  ['+ column_name +'] ' +
            data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'image' then ''
            when 'decimal' then '(' + cast (numeric_precision as varchar) + ', ' + cast (numeric_scale as varchar) + ')'
            else coalesce ('('+ case when character_maximum_length = -1 then 'MAX' else cast (character_maximum_length as varchar) end +')', '') end + ' ' +
            case when exists (
            select id from syscolumns
            where object_name(id)=so.name
            and name = column_name
            and columnproperty(id, name, 'IsIdentity') = 1
            ) then
            'IDENTITY(' +
            cast (ident_seed(so.name) as varchar) + ',' +
            cast (ident_incr(so.name) as varchar) + ')'
            else ''
            end + ' ' +
            (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
            case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
            from information_schema.columns where table_name = so.name
            order by ordinal_position
            FOR XML PATH ('')) o (list)
            left join
            information_schema.table_constraints tc
        on tc.Table_name = so.Name
            AND tc.Constraint_Type = 'PRIMARY KEY'
            cross apply
            (select '[' + Column_Name + '], '
            FROM information_schema.key_column_usage kcu
            WHERE kcu.Constraint_Name = tc.Constraint_Name
            ORDER BY
            ORDINAL_POSITION
            FOR XML PATH ('')) j (list)
        where xtype = 'U'
          AND name = 'table_name'  -- table_name 换成你要查询的表名