生成sql server数据库字典

执行下面sql语句，然后导出结果到Excel，可以当做是数据库字典

 SELECT sysobjects.NAME                                                    AS
       表名称,
       --------------as 的作用:为字段起一个别名
       --sysproperties.[value] AS 表说明 , ----------[ ]方括号的作用:为了避免和关键字冲突
       syscolumns.NAME                                                    AS
       字段名称,
       --properties.[value] AS 字段说明 ,
       systypes.NAME                                                      AS
       字段类型,
       syscolumns.length                                                  AS
       字段长度,
       Isnull(Columnproperty(syscolumns.id, syscolumns.NAME, 'Scale'), 0) AS
       小数位数,
       --isnull---(待测数据,返回值).当待测数据为空时返回返回值,当大策数据为非null时,反回待测数据
       CASE
         WHEN syscolumns.isnullable = 0 THEN ''
         ELSE ' √ '
       END                                                                AS
       是否为空,
       CASE
         WHEN syscomments.text IS NULL THEN ''
         ELSE syscomments.text
       END                                                                AS
       缺省值,
       CASE
         WHEN Columnproperty(syscolumns.id, syscolumns.NAME, 'IsIdentity') = 1
       THEN
         ' √ '
         ELSE ''
       END                                                                AS
       递增字段,
       CASE
         WHEN sysindexes.NAME IS NULL THEN ''
         ELSE sysindexes.NAME
       END                                                                AS
       索引名称,
       CASE
         WHEN sysindexkeys.keyno IS NULL THEN ''
         ELSE CONVERT(VARCHAR(10), sysindexkeys.keyno)
       END                                                                AS
       索引位置,
       CASE
         WHEN sysindexes.indid = 1 THEN ' 聚集索引 '
         WHEN sysindexes.indid > 1
              AND sysindexes.indid <> 255 THEN ' 非聚集索引 '
         WHEN sysindexes.indid IS NULL THEN ''
         ELSE ' 其他 '
       END                                                                AS
       索引类型,
       CASE
         WHEN EXISTS (SELECT 1
                      FROM   sysobjects
                      WHERE  xtype = 'PK'
                             AND NAME IN (SELECT NAME
                                          FROM   sysindexes
                                          WHERE  indid IN
                                         (SELECT indid
                                          FROM   sysindexkeys
                                          WHERE  id = syscolumns.id
                                                 AND colid =
                                         syscolumns.colid))) THEN
         ' √ '
         ELSE ''
       END                                                                AS
       主键,
       CASE
         WHEN sysforeignkeys.constid IS NULL THEN ''
         ELSE ' √ '
       END                                                                AS
       外健
FROM   syscolumns -- 数据表字段
       INNER JOIN sysobjects -- 数据对象
               ON sysobjects.id = syscolumns.id
       INNER JOIN systypes -- 数据类型
               ON syscolumns.xtype = systypes.xtype
       LEFT OUTER JOIN syscomments -- 注释信息
                    ON syscolumns.cdefault = syscomments.id
       LEFT OUTER JOIN sysindexkeys -- 索引中的键或列的信息
                    ON sysindexkeys.id = syscolumns.id
                       AND sysindexkeys.colid = syscolumns.colid
       LEFT OUTER JOIN sysindexes -- 数据库 索引表
                    ON sysindexes.id = sysindexkeys.id
                       AND sysindexes.indid = sysindexkeys.indid
       LEFT OUTER JOIN sysforeignkeys
                    ON sysforeignkeys.fkeyid = syscolumns.id
                       AND sysforeignkeys.fkey = syscolumns.colid
WHERE  ( sysobjects.xtype = 'U' )
ORDER  BY sysobjects.id,
          syscolumns.colid