Dynamics CRM 2013 常用SQL查询基础数据

获取实体

SELECT * FROM Entity WHERE LogicalName='EntityName'

获取字段名称

SELECT distinct A.name AS 字段名,L.label AS 显示名,AT.description AS 类型, L.ObjectColumnName AS 形式,A.IsNullable AS code from
attribute A 
INNER JOIN localizedlabel L
ON A.Attributeid = L.objectid 
INNER JOIN  AttributeTypes AT 
ON a.AttributeTypeId=AT.AttributeTypeId
INNER JOIN entity E
ON E.entityid = A.entityid
WHERE 
L.languageid='2052' and  --中文
E.name='EntityName' and  --实体名称
L.ObjectColumnName='DisplayName' --显示名称

获取选项集

SELECT E.ObjectTypeCode, AttributeName AS 字段名称,AttributeValue AS 选项集值,Value AS 选项集显示文本  FROM StringMap AS map  with(nolock) 
INNER JOIN Entity AS E ON E.ObjectTypeCode = map.ObjectTypeCode
WHERE E.LogicalName = 'LogicalName' AND AttributeName = 'AttributeName'

查询已保存的视图

SELECT * FROM SavedQuery

查询个人视图

SELECT * FROM UesrQuery

查询用户所拥有的安全角色

SELECT R.Name AS 角色,u.DomainName AS 账号, u.FullName AS 名称 
FROM SystemUserRoles AS ur
INNER JOIN SystemUserBase AS u on u.SystemUserId = ur.SystemUserId
LEFT JOIN BusinessUnitBase bu on bu.BusinessUnitId = u.BusinessUnitId
LEFT JOIN Role r on r.RoleId = ur.RoleId
WHERE u.IsDisabled = 0 AND u.DomainName = '登录名'
ORDER BY R.Name