SQL get picklist details (CRM 4.0)

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 JOIN       AttributeView
ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
RIGHT OUTER JOIN       EntityView
INNER JOIN       LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId
ON       AttributeView.EntityId = EntityView.EntityId
INNER JOIN attributetypes on AttributeView.AttributeTypeId = AttributeTypes.AttributeTypeId
AttributePicklistValueView ON AttributeView.AttributeId = AttributePicklistValueView.AttributeId
StringMap ON StringMap.AttributeValue = AttributePicklistValueView.Value
AND StringMap.AttributeName = AttributeView.Name
AND StringMap.ObjectTypeCode = EntityView.ObjectTypeCode
AND EntityView.EntityId = AttributeView.EntityId
WHERE   LocalizedLabelView_1.ObjectColumnName = ‘LocalizedName’ AND LocalizedLabelView_2.ObjectColumnName = ‘DisplayName’
AND LocalizedLabelView_1.LanguageId = ‘1033’ AND LocalizedLabelView_2.LanguageId = ‘1033’
ORDER BY EntityDisplayName, AttributeName

This entry was posted in Helpers, SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s