sqlserver 生成数据字典

1，

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 ;

 

2，

SELECT
sysobjects.name AS 表名称 ,
syscolumns.name 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 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 sys.extended_properties properties -- 字段属性信息
ON syscolumns.id = properties.major_id
AND syscolumns.colid = properties.minor_id
LEFT OUTER JOIN sys.extended_properties sysproperties -- 表属性信息
ON sysobjects.id = sysproperties.major_id
AND sysproperties.minor_id = 0
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