sql server 导出库表结构的sql语句

103 阅读 0 评论 68 点赞

我是靠谱客的博主痴情花生，最近开发中收集的这篇文章主要介绍sql server 导出库表结构的sql语句，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1：sql server 2000

SELECT

表名=d.name,

表说明=isnull(f.value,''),

字段序号=a.colorder,

字段名=a.name,

标识=case when a.colstat=1 then '√'else '' end,

主键=case when exists(select 1 from sysindexes y,sysindexkeys z where y.id=z.id and y.indid=z.indid and z.id=a.id and z.colid=a.colid and y.status & 2948=2048) then '√'else '' end,

类型=b.name,

占用字节数=a.length,

精度=a.prec,

小数位数=isnull(a.Scale,0),

允许空=case when a.isnullable=1 then '√'else '' end,

默认值=isnull(e.text,''),

字段说明=isnull(g.[value],'')

FROM syscolumns a

left join systypes b on a.xtype=b.xusertype

inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e on a.cdefault=e.id

left join sysproperties g on a.id=g.id and a.colid=g.smallid

left join sysproperties f on a.id=f.id and f.smallid=0

-- where d.name='tablename' --如果只查询指定表,加上此条件

order by a.id,a.colorder

2、sql server 2008

SELECT obj.name tablename,  col.name,
                        ep.[value] columnName,
                        t.name as type,
                        col.length,
                        COLUMNPROPERTY(col.id, col.name, 'Scale') scale,
                         case when COLUMNPROPERTY(col.id, col.name, 'IsIdentity')=1 then '√' else '' end IsIdentity,
                        CASE WHEN EXISTS ( SELECT   1
                                           FROM     dbo.sysindexes si
                                                    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
                                                    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
                                                    INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
                                           WHERE    sc.id = col.id AND sc.colid = col.colid ) THEN '√'
                             ELSE ''
                        END AS pk,
                        CASE WHEN col.isnullable = 1 THEN '√'
                             ELSE ''
                        END AS nullable,
                        comm.text AS defaultvalue
                FROM    dbo.syscolumns col
                        LEFT OUTER JOIN dbo.systypes t ON col.xtype = t.xusertype
                        INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype in ('U','V') AND obj.status >= 0
                        LEFT OUTER JOIN dbo.syscomments comm ON col.cdefault = comm.id
                        LEFT OUTER JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'
                        LEFT OUTER JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description'
                --WHERE   obj.id=1634104862
                ORDER BY obj.id,col.colorder