CRM相关SQl手记

--1.查询某个实体的主要字段
SELECT [AttributeId] AS [attributeid],
	[Name] AS [name] FROM [AttributeAsIfPublishedView] 
	WHERE EntityId = '39C2A7D3-6689-4CD5-8673-30545D918D6C'
	 AND DisplayMask & 256 = 256
--2.根据实体Code查询实体信息
select top 1 EntityId,LogicalName From MetadataSchema.Entity where ObjectTypeCode=1
--3.获取实体选项集类型的字段	
Select distinct llv.Label,apv.Value From 
(
	Select top 1 * From  MetadataSchema.Entity Where entity.Name = 'account'
) entity  
Inner join dbo.AttributeLogicalView alv on entity.EntityId=alv.EntityId  
Inner join  AttributePicklistValueView apv on alv.OptionSetId=apv.OptionSetId  
Inner join LocalizedLabelView llv On apv.AttributePicklistValueId=llv.ObjectId  
And alv.Name='crm_is_group' and llv.ObjectColumnName='DisplayName'
--4.查询某个实体的某个字段的字段名称
select alv.AttributeId,Name,PhysicalName From AttributeLogicalView alv 
inner join
(
	select top 1 EntityId From MetadataSchema.Entity where ObjectTypeCode=1
)et on alv.EntityId=et.EntityId
where ColumnNumber in(9,10029,10025)
--5.查询实体的中文和英文内容
Select e.Name,ll.Label 
from MetadataSchema.LocalizedLabel ll
Inner join MetadataSchema.Entity e
	On ll.ObjectId=e.EntityId And ll.SolutionId=e.SolutionId
Inner join Solution s
	On ll.SolutionId=s.SolutionId
Where (s.UniqueName='Active' )
	and ll.ObjectColumnName='LocalizedName'
And e.Name='systemuser'