Category Archives: SQL

SQL Security Roles information

Posted on May 21, 2013 by mrashwan

Here is an SQL to retrieve all security roles privileges for all entities to help with comparing or to be used in an SSRS report SELECT FilteredRole.name as UserName, EntityView.PhysicalName, EntityView.OriginalLocalizedName as DisplayName, Privilege.Name as PrivilegeName, AccessLevel = CASE Privilege.AccessRight … Continue reading →

Posted in Helpers, SQL | Tagged , , | Leave a comment

SQL get picklist details (CRM 4.0)

Posted on May 21, 2013 by mrashwan

The following SQL can be used to retrieve information about picklists in CRM 4.0 SELECT  LocalizedLabelView_1.Label AS EntityDisplayName, EntityView.Name AS EntityName, LocalizedLabelView_2.Label AS AttributeDisplayName, AttributeView.Name AS AttributeName , StringMap.AttributeValue, StringMap.Value AS AttributeValueCaption, AttributeTypes.Description as Type,AttributeTypes.XmlType,AttributeView.Length,Accuracy,IsCustomEntity,IsMappable,EntityView.IsCustomizable,EntityView.IsRenameable,IsActivity,AttributeView.AttributeRequiredLevelId FROM    LocalizedLabelView AS LocalizedLabelView_2 INNER … Continue reading →

Posted in Helpers, SQL | Tagged , , , , , | Leave a comment

SQL get optionset details

Posted on May 21, 2013 by mrashwan

The followin SQL can be used to retrieve information about optionsets in all entitesSELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName, AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName, Stringmap.Value,Stringmap.AttributeValue,StringMap.DisplayOrder, AttributeTypes.Description as Type, AttributeTypes.XmlType,AttributeView.Length,Accuracy,IsCustomEntity,IsMappable,EntityView.IsCustomizable,EntityView.IsRenameable,IsActivity FROM    LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN       AttributeView ON LocalizedLabelView_2.ObjectId … Continue reading →

Posted in Helpers, SQL | Tagged , , , , | Leave a comment

SQL get attributes details of entities

Posted on May 21, 2013 by mrashwan

The following SQL can be used to retrieve information and details about all attributes in an entity. SELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName, AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName,AttributeTypes.Description as Type, AttributeTypes.XmlType,AttributeView.Length,AttributeView.MaxLength,AttributeView.AttributeRequiredLevelId,Accuracy,IsCustomEntity,IsMappable,EntityView.IsCustomizable,EntityView.IsRenameable,IsActivity FROM    LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN       AttributeView … Continue reading →

Posted in Helpers, SQL | Leave a comment