获取sqlserver数据字典的完整sql

方法一：

 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;