Fields in a CRM entity record are often based on a pick list. Should you need to query the SQL database directly to extract information these will come out not as the text based descriptive values, but a code such as 0, 1, or 2. To retrieve the associated descriptive information, a join to the StringMapBase table is required. A rough SQL query might be something like so:
select AttributeName, AttributeValue, Value from StringMapBase where LangId = 1033 and AttributeName = 'TheNameOfTheAttribute'
This works fine for most items, but some such as statecode are used in multiple entities. A join to the MetaDataSchema.Entity table to get the ObjectTypeCode will filter down the records. If you do not the following will result:
Say you wanted to retrieve the values for statecode for the Opportunity entity, you would write query such as this:
select ObjectTypeCode, EntityId, Name from MetadataSchema.Entity where Name = 'Opportunity' and OverwriteTime = '1900-01-01'
The reason for the date filter is that CRM maintains a history of changes to the entities. If a customisation solution has been applied to your CRM instance, more than one record will exist for some entities.
Joining the two queries together:
select t1.AttributeName, t1.AttributeValue, t1.Value from StringMapBase t1 inner join MetadataSchema.Entity t2 on t1.ObjectTypeCode = t2.ObjectTypeCode where t2.Name = 'Opportunity' and t1.AttributeName = 'statecode' and t2.OverwriteTime = '1900-01-01'
The resulting data set looks much better:
The LangId filter in all the queries is simply filtering for the English language – this is needed for multi-lingual systems.