java数据字典tag,数据字典

摘要:

下文讲述使用sqlserver系统视图

dbo.sysindexes dbo.sysindexkeys dbo.syscolumns dbo.sysobjects dbo.systypes dbo.syscomments sys.extended_properties

生成数据字典的方法，如下所示:

实验环境:sqlserver 2008

sqlserver 2008生成数据字段的方法，如下所示:

SELECT a.id as [对象Id],

CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS [表名称],

CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS [表备注],

a.colorder AS [字段顺序号], a.name AS [字段名称],

CASE WHEN COLUMNPROPERTY(a.id,

a.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 = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS [是否主键],

b.name AS [字段类型], a.length AS [字段长度], COLUMNPROPERTY(a.id, a.name, 'PRECISION')

AS [字段精度], ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS [字段小数位数],

CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS [是否允许null], ISNULL(e.text, '')

AS [字段默认值], ISNULL(g.[value], '') AS [字段备注], d.crdate AS [对象创建时间],

CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS [对象修改时间]

FROM dbo.syscolumns a LEFT OUTER JOIN

dbo.systypes b ON a.xtype = b.xusertype INNER JOIN

dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND

d.status >= 0 LEFT OUTER JOIN

dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN

sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND

g.name = 'MS_Description' LEFT OUTER JOIN

sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND

f.name = 'MS_Description'

ORDER BY d.name, [字段顺序号]

mssql_sqlserver_sqlserver表字典生成方法分享

sqlserver 所有对象字典生成(视图、表、存储过程、 触发器、函数、 主外键、约束、规则)，如下所示:

SELECT isnull(b.name,'') AS [所属对象], a.xtype as [对象类型标识],

CASE a.xtype

WHEN 'C' THEN '约束'

WHEN 'D' THEN '默认值'

WHEN 'F' THEN '外键约束'

WHEN 'L' THEN '日志'

WHEN 'FN' THEN '标量函数'

WHEN 'IF' THEN '内嵌表函数'

WHEN 'P' THEN '存储过程'

WHEN 'PK' THEN '主键约束'

WHEN 'RF' THEN '复制筛选存储过程'

WHEN 'S' THEN '系统表'

WHEN 'TF' THEN '表函数'

WHEN 'TR' THEN '触发器'

WHEN 'U' THEN '用户表'

WHEN 'UQ' THEN 'UNIQUE 约束'

WHEN 'V' THEN '视图'

WHEN 'X' THEN '扩展存储过程'

WHEN 'R' THEN '规则' ELSE NULL

END AS [类型名称], a.name AS [对象名称], a.crdate AS [新建时间], a.refdate AS [修改时间],

c.text AS [sql脚本],OBJECTPROPERTY(a.id, N'IsMSShipped')

FROM dbo.sysobjects a Left JOIN

dbo.sysobjects b ON a.parent_obj = b.id LEFT OUTER JOIN

dbo.syscomments c ON a.id = c.id

WHERE

(OBJECTPROPERTY(a.id, N'IsMSShipped') = 0) AND (isnull(b.name,'') <> N'dtproperties')

ORDER BY a.xtype DESC

mssql_sqlserver_sqlserver表字典所有对象生成方法分享