-获取实体的etc码
SELECT ObjectTypeCode,Name,EntityId FROM MetadataSchema.Entity where Name='list'
--修改选项集的LABEL(修改之后重启IIS)
SELECT DISTINCT
a.Value ,
b.Label ,
b.ObjectId
FROM AttributePicklistValue a
JOIN LocalizedLabel b ON a.AttributePicklistValueId = b.ObjectId
JOIN Attribute c ON c.optionsetid = a.optionsetid
JOIN Entity d ON d.EntityId = c.EntityId
WHERE d.Name = 'activitypointer'
AND c.Name = 'prioritycode'
AND a.Value = '1'
UPDATE LocalizedLabel SET label = '正常' WHERE objectid='4CE9AF0C-2341-DB11-898A-0007E9E17EBD' AND languageid='2052'
--获取Picklist标签
SELECT attributename ,
attributevalue ,
VALUE
FROM stringmap s
INNER JOIN entityview e ON s.objecttypecode = e.objecttypecode
WHERE e.name = 'new_project'
AND AttributeName = 'new_purposelist'
--窗体的安全角色
select * from SystemForm WHERE ObjectTypeCode=3
--用户的首选设置。
SELECT HomepageArea,HomepageSubarea,* FROM FilteredUserSettings
--附件
select * from activityattachment
--系统视图
SELECT SavedQueryid,name，FetchXml FROM SavedQuery WHERE Name='可用联系人'
--个人视图
SELECT * FROM UserQuery
--字段安全性配置文件
SELECT * FROM FieldSecurityProfile
--字段权限
SELECT * FROM fieldpermission
--权限
select Name from PrivilegeBase where PrivilegeId = "a8ecac53-09e8-4a13-b598-8d8c87bc3d33"
--查找用户开通的权限
SELECT * FROM dbo.SystemUserRoles WHERE SystemUserId='C6AA5729-20C8-E211-A872-00155D01D325'
--商机丢单（竞争对手写入位置）
SELECT RegardingObjectId ,
RegardingObjectIdName ,
CompetitorId ,
StatusCode ,
StateCode ,*
FROM ActivityPointerBase
WHERE RegardingObjectId = 'D7167DEF-CAAF-E211-9C4E-00155D01D332'
AND StateCode = 1 AND StatusCode=2
---获取实体共享数据权限（以实体opportunity为例） accessrightsmask =1 为ACCOUNT共享，
InheritedAccessRightsMask有值代表是父实体共享而来, Objecttypecode是每个实体的编号
SELECT u.FullName ,
a.name ,
a. OwnerIdName,
sup.SystemUserId ,
POA.ObjectId ,
AccessRightsMask ,
InheritedAccessRightsMask
FROM PrincipalObjectAccess POA
INNER
JOIN SystemUserPrincipals sup ON POA.PrincipalId = sup.PrincipalId
INNER JOIN SystemUserBase u ON sup.SystemUserId = u.SystemUserId
INNER JOIN opportunity a ON a.opportunityId = POA.ObjectId
WHERE POA.objecttypecode = 3
AND ( ( POA.AccessRightsMask | POA.InheritedAccessRightsMask ) & 1 ) = 1
AND POA.ObjectId = '88493FD1-C6C2-E211-A784-00155D01D332'
--取消实体共享
UPDATE PrincipalObjectAccess
SET AccessRightsMask =0, InheritedAccessRightsMask=0
WHERE ObjectTypeCode=3
--获取字段的类型（字段new_projectname）
SELECT a.name ,
t.DESCRIPTION ,
t.xmltype ,
t.sqlservertype ,
e.Name
FROM attributeview a
INNER JOIN metadataschema.attributetypes t ON a.attributetypeid = t.attributetypeid
INNER JOIN metadataschema.Entity e ON a.EntityId = e.EntityId
WHERE a.Name = 'new_projectname'
--获取字段名称（字段new_cdjd）
SELECT a.Name,e.Name,entitydisplaynames.Label
FROM dbo.attributeview a
INNER JOIN dbo.localizedlabelview entitydisplaynames ON entitydisplaynames.objectid = a.attributeid
INNER JOIN MetadataSchema.Entity e ON a.EntityId=e.EntityId
WHERE entitydisplaynames.objectcolumnname = 'displayname'
AND a.name='new_cdjd'
--活动第三方实体
SELECT * FROM ActivityParty WHERE ActivityId='DD54B910-15B6-E211-9C4E-00155D01D332'
--字段安全性的相关表
SELECT * FROM systemuserprofiles