mssql 使用sqlserver 系统视图,生成数据字典的方法分享


摘要:
下文讲述使用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表字典生成方法分享

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表字典所有对象生成方法分享

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