获取sqlserver数据字典的完整sql

亦凉 2021-06-26 16:06 326阅读 0赞

## 方法一： ##

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

## 方法二： ##

SELECT obj.NAME                                             AS 表名,
           col.colorder                                         AS 序号,
           col.NAME                                             AS 列名,
           Isnull(ep.[value], '')                               AS 列说明,
           t.NAME                                               AS 数据类型,
           col.length                                           AS 长度,
           Isnull(Columnproperty(col.id, col.NAME, 'Scale'), 0) AS 小数位数,
           CASE
             WHEN Columnproperty(col.id, col.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 = col.id
                                 AND sc.colid = col.colid) THEN '√'
             ELSE ''
           END                                                  AS 主键,
           CASE
             WHEN col.isnullable = 1 THEN '√'
             ELSE ''
           END                                                  AS 允许空,
           Isnull(comm.text, '')                                AS 默认值
    FROM   dbo.syscolumns col
           LEFT JOIN dbo.systypes t
                  ON col.xtype = t.xusertype
           INNER JOIN dbo.sysobjects obj
                   ON col.id = obj.id
                      AND obj.xtype = 'U'
                      AND obj.status >= 0
           LEFT JOIN dbo.syscomments comm
                  ON col.cdefault = comm.id
           LEFT JOIN sys.extended_properties ep
                  ON col.id = ep.major_id
                     AND col.colid = ep.minor_id
                     AND ep.NAME = 'MS_Description'
           LEFT JOIN sys.extended_properties epTwo
                  ON obj.id = epTwo.major_id
                     AND epTwo.minor_id = 0
                     AND epTwo.NAME = 'MS_Description'
    --WHERE obj.name = 'scene_project_product_station_product_check_detail_extend'--表名
    ORDER  BY obj.NAME;