获取SQLServer的最完整数据字典的SQL语句

最新推荐文章于 2022-06-14 16:34:49 发布

weixin_34396902

最新推荐文章于 2022-06-14 16:34:49 发布

阅读量155

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_34396902/article/details/85145320

本文提供了一套全面的SQL语句，用于从SQLServer中提取详细的表结构信息，包括表名、字段名、字段类型、长度、是否为空、是否为自增字段、索引详情等，便于创建数据字典。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

获取SQLServer 的最完整数据字典的SQL 语句

其实网上已经流传了很多关于获取 SQLServer 的数据字典的版本，不过我相信这个应该是最全的了，本语句包括了表、字段、字段类型、字段长度、是否为空、是否递增字段、索引名称、索引的定位、索引类型、主键、外键等；通过合理的裁剪可以很方便的生成相应的数据字典。

SELECT

sysobjects.name 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 小数位数 ,

CASE WHEN syscolumns.isnullable=0
THEN ''
ELSE ' √ 'END AS 是否为空 ,
CASE WHEN syscomments.text IS
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 ''ITPUBELSE 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
' 其他 '
END AS 索引类型 ,CASE WHEN EXIST
(SELECT
FROM sysobjects
WHERE xtype = 'PK' AND name
(SELECT name
     FROM sysindexes
WHERE ind
   (SELECT indid
      FROM sysindexkeys
      WHERE id = syscolumns.id AND colid = syscolumns.colid)
THEN ' √ ' ELSE ''
END AS 主键 ,

CASE WHEN sysforeignkeys.constid IS NULL

END AS 外健
FROM syscolumns                            -- 数据表字段
INNER JOIN sysobjects                        -- 数据对象
ON sysobjects.id = syscolumns.id
INNER JOIN systypes                         -- 数据类型
ON syscolumns.xtype = systypes.xtype
LEFT OUTER JOIN sysproperties properties       -- 字段属性信息
ON syscolumns.id = properties.idI
AND syscolumns.colid = properties.smallid
LEFT OUTER JOIN sysproperties                -- 表属性信息
ON sysobjects.id = sysproperties.id
AND sysproperties.smallid = 0
LEFT OUTER JOIN syscomments                -- 注释信息
ON syscolumns.cdefault = syscomments.
LEFT OUTER JOIN sysindexkeys                -- 索引中的键或列的信息
ON sysindexkeys.id = syscolumns

AND sysindexkeys.colid = syscolumns.col
LEFT OUTER JOIN sysindexes -- 数据库索引表
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid = sysindexkeys.indid
LEFT OUTER JOIN sysforeignkeysI
ON sysforeignkeys.fkeyid = syscolumns.id
AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype = 'U')
order by sysobjects.id,syscolumns.colid