如何使用MSCRM中的SQL查询获取所有实体及字段的显示名称,逻辑名称和其他信息的列表
1.使用以下查询获取实体显示名称,逻辑名称和对象类型代码的所有列表。
SELECT DISPLAYNAME.LABEL 'Display Name', EV.NAME 'Logical Name', ObjectTypeCode 'Object Type Code' FROM ENTITYVIEW EV INNER JOIN LOCALIZEDLABELLOGICALVIEW DISPLAYNAME ON (EV.ENTITYID = DISPLAYNAME.OBJECTID) AND (DISPLAYNAME.OBJECTCOLUMNNAME = 'LOCALIZEDNAME') WHERE LANGUAGEID = 1033 -- 如果要使用语言ID过滤记录,请添加此代码. 例如: LANGUAGEID 1033 是英语 --AND ISCUSTOMENTITY = 1 -- 如要还要显示系统实体,请将其删除 --AND ISACTIVITY = 0 -- 如果要过滤活动实体,请添加此选项 --AND EN.NAME NOT LIKE '%MSDYN%' -- 添加like条件等 ORDER BY 1
2.使用以下查询获取实体字段的显示名称,逻辑名称。
SELECT EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName
FROM LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN
AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN
EntityView INNER JOIN
LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON
AttributeView.EntityId = EntityView.EntityId
WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'
AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
AND LocalizedLabelView_1.LanguageId = '1033'
AND LocalizedLabelView_2.LanguageId = '1033'
AND EntityView.Name IN ('Contact')
ORDER BY EntityName, AttributeName