sqlServer 查询所有的字段信息，数据字典

SELECT TOP 100 PERCENT --a.id, 
CASE
  WHEN a.colorder = 1 THEN d.NAME
  ELSE ''
END                                              AS 表名,
CASE
  WHEN a.colorder = 1 THEN Isnull(f.value, '')
  ELSE ''
END                                              AS 表说明,
a.colorder                                       AS 字段序号,
a.NAME                                           AS 字段名,
CASE
  WHEN Columnproperty(a.id, a.NAME, 'IsIdentity') = 1 THEN '√'
  ELSE ''
END                                              AS 标识,
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 = a.id
                      AND sc.colid = a.colid) THEN '√'
  ELSE ''
END                                              AS 主键,
b.NAME                                           AS 类型,
a.length                                         AS 长度,
Columnproperty(a.id, a.NAME, 'PRECISION')        AS 精度,
Isnull(Columnproperty(a.id, a.NAME, 'Scale'), 0) AS 小数位数,
CASE
  WHEN a.isnullable = 1 THEN '√'
  ELSE ''
END                                              AS 允许空,
Isnull(e.text, '')                               AS 默认值,
Isnull(g.[value], '')                            AS 字段说明,
d.crdate                                         AS 创建时间,
CASE
  WHEN a.colorder = 1 THEN d.refdate
  ELSE NULL
END                                              AS 更改时间
FROM   dbo.syscolumns a
       LEFT OUTER JOIN dbo.systypes b
                    ON a.xtype = b.xusertype
       INNER JOIN dbo.sysobjects d
               ON a.id = d.id
                  AND d.xtype = 'U'
                  AND d.status >= 0
       LEFT OUTER JOIN dbo.syscomments e
                    ON a.cdefault = e.id
       LEFT OUTER JOIN sys.extended_properties g
                    ON a.id = g.major_id
                       AND a.colid = g.minor_id
                       AND g.NAME = 'MS_Description'
       LEFT OUTER JOIN sys.extended_properties f
                    ON d.id = f.major_id
                       AND f.minor_id = 0
                       AND f.NAME = 'MS_Description'
ORDER  BY d.NAME,
          字段序号