Get OptionSet Values from CRM Database - StringMap in CRM

Hi,
 
At times we get requirement to query and get option set items from CRM and show them in a report or in some custom application. Then we query to SQL MSCRM Organization database to get the pick list values but we get values as 0, 1, 2 and 1000001 etc.. but where we need to show the text values.
 
To retrieve the associated descriptive information, we need ajoin to the StringMapBase table.
 
SELECT
       ATTRIBUTENAME,
       ATTRIBUTEVALUE,
       VALUE
FROM
       STRINGMAPBASE
WHERE
       LANGID = 1033 AND ATTRIBUTENAME = 'statecode' 

 

This works absolutely fine for the unique optionsets but not for StateCode. For statecode, we need to have a condition on objecttypecode. Here is the query to get the objecttypecode of the entity.
 
SELECT
       OBJECTTYPECODE,
       ENTITYID,
       NAME
FROM
       METADATASCHEMA.ENTITY
WHERE
       NAME = 'Opportunity' AND OVERWRITETIME = '1900-01-01' 

The reason for date filter is CRM maintains a history of changes to the entities. If a customization solution has been applied to your CRM instance, more than one record will exist for some entities.

Here is the query after joining.

SELECT

       strMap.ATTRIBUTENAME,
       strMap.ATTRIBUTEVALUE,
       strMap.VALUE
FROM
       STRINGMAPBASE strMap
INNER JOIN
       METADATASCHEMA.ENTITY ent ON strMap.OBJECTTYPECODE = ent.OBJECTTYPECODE
WHERE
       ent.NAME = 'Opportunity'
       AND strMap.ATTRIBUTENAME = 'statecode'
       AND ent.OVERWRITETIME = '1900-01-01'

The LangId filter in all the queries is simply filtering for the English language – this is needed for multi-lingual systems.
 
Hope this helps.
 
--
Happy CRM'ing
Gopinath