Dynamics CRM 365 查询实体之间的关系---SQL篇

crmsql语句查询实体窗体实体字段关系信息

 

 

在crm里面如何用sql语句查询这些信息？

查询实体信息：

--查询实体信息,实体名称：account
select * from MetadataSchema.Entity where name= 'account'

 

 

 

查询窗体信息：

--查询窗体信息,ObjectTypeCode: 实体的code
select * from systemform where ObjectTypeCode = 1

 

 

 

查询视图信息：

--查询视图信息,ReturnedTypeCode:实体的code
select * from SavedQuerybase where ReturnedTypeCode = 1

 

 

 

 

查询字段信息：

--查询字段信息
with attr as(
select * from MetadataSchema.Attribute as a
where a.EntityId in (select entityid from MetadataSchema.Entity
where name = 'new_member') and a.IsCustomField = 1
)
select a.attributeid,a.Name,label.Label,ty.Description,
(select top 1 name from MetadataSchema.Entity where EntityId in (
select ship.ReferencedEntityId from MetadataSchema.Relationship as ship
where ship.ReferencingAttributeId = a.AttributeId)) as 'lookName'
from attr as a inner join MetadataSchema.LocalizedLabel as label
on a.attributeid = label.objectid inner join MetadataSchema.AttributeTypes as ty
on a.AttributeTypeId = ty.AttributeTypeId
where label.objectcolumnname = 'DisplayName' and a.validforcreateapi = 1

 

 

 

查询关系信息：
select * from MetadataSchema.Relationship where ReferencingEntityId in
(select top 1 entityid from MetadataSchema.Entity where name = 'new_store')

 

 

 

原文链接：https://blog.csdn.net/y_f123/article/details/37955601