Microsoft Dynamics 365 微软CRM 导出数据字典 表结构

背景

dynamics 365 是在系统上创建实体(表/视图), 并不是直接在数据库操作, 以至于, 我们在查看/直连数据库的时候要查看某个字段是做什么的非常不方便.

思路

博主不止一次被公司其他部门索要数据字典, 但是dynamics 365并没有一个好用的可以导出数据字典的方式, 但是发现视图基本都是存储在 MetadataSchema.XXX 表里

• MetadataSchema.Entity 存储表/视图
• MetadataSchema.Attribute 存储字段
• MetadataSchema.AttributeTypes 存储数据类型
• MetadataSchema.LocalizedLabel 存储字段备注
• StringMap 存储多选/单选的值和对应说明

需要注意的是以上表都是一对多,由于版本, 是否发布等多种原因,可能查出来有重复的数据, 需要注意筛选, 如果可以, 也非常希望各位持续优化, 由于并没有找到官方对应的文档, 基本都是自己摸索, 持续优化中

于是思路是通过SQL语句查询生成数据字典

博主使用的Microsoft Dynamics 365版本是 1612 (9.0.24.8) (DB 9.0.24.8) (本地), 不同的版本可能数据结构不一定相同, 可能需要看情况优化

SQL语句

SELECT ent.BaseTableName             AS '表名',
       ent.Name                      AS '视图名',
       ent.OriginalLocalizedName     AS '表说明',
       attr.Name                     AS '字段名',
       tps.Description               AS '字段类型',
       attr.Length                   AS '字段长度',
       CASE attr.IsNullable
         WHEN 1 THEN ''
         ELSE '√'
       END                           AS '是否必填',
       attr.DefaultValue             '默认值',
       CASE attr.IsCustomField
         WHEN 1 THEN '√'
         ELSE ''
       END                           AS '是否为自定义字段',
       CASE attr.IsPKAttribute
         WHEN 1 THEN '√'
         ELSE ''
       END                           AS '是否为主键',
       (SELECT TOP 1 lab.Label
        FROM   MetadataSchema.LocalizedLabel AS lab
        WHERE  attr.AttributeId = lab.ObjectId
        ORDER  BY lab.LabelTypeCode) AS '字段说明',
       CASE
         WHEN tps.Description NOT IN ( 'picklist', 'state', 'status', 'multiselectpicklist' ) THEN ''
         ELSE Stuff((SELECT ','
                            + CONVERT(VARCHAR(100), sm.AttributeValue)
                            + ':' + sm.Value
                     FROM   StringMap sm
                     WHERE  ent.ObjectTypeCode = sm.ObjectTypeCode
                            AND attr.Name = sm.AttributeName
                     FOR XML PATH('')), 1, 1, '')
       END                           AS '选项说明'
FROM   MetadataSchema.Entity AS ent
       JOIN MetadataSchema.Attribute AS attr
         ON ent.EntityId = attr.EntityId
       JOIN MetadataSchema.AttributeTypes AS tps
         ON attr.AttributeTypeId = tps.AttributeTypeId
WHERE  ent.Name IN ( 'new_store' ) --筛选表名
       --AND attr.IsCustomField = 1 --筛选自定义字段
ORDER  BY ent.Name,
          attr.Name 

结果