How to get list of OptionSet fields for an entity and their text and values from the database- MS CRM 2011 RRS feed

  • Question

  • Hi All,

    I have the requirement to get list OptionSet fields for an entity and their text and values from the database in MS CRM 2011.

    It would be great if you can provide the SQL Script to get these details.


    Srikanth Reddy

    Thursday, October 2, 2014 6:50 AM

All replies

  • Hi,

    Try this

    select * from FilteredStringMap
    where FilteredViewName is not null
    order by FilteredViewName, AttributeName, DisplayOrder

    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Thursday, October 2, 2014 8:45 AM
  • Thanks for your quick response, Is there any SQL Query to get entity name and it's related optionset fields

    and then OptionSet value and text, I have tried in several to get it, but no luck.

    Could you please provide the solution for this requirement.

    thanks in advance

    Srikanth Reddy 

    Saturday, October 4, 2014 6:28 AM
  • Hi Srikanth,

    Use below SQL Query

    SELECT DISTINCT E.Name,S.AttributeName,S.AttributeValue,S.Value from stringmap S
    INNER JOIN Entity E
    ON S.ObjectTypeCode = E.ObjectTypeCode

    Hope it helps,

    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer". Thanks and Regards, Mohammad Yusuf Ansari http://microxrm.blogspot.in

    Saturday, October 4, 2014 10:25 AM
  • Hi Srikanth,

     The following query will get the attribute with name 'new_test' from Contact Entity.

    select * from FilteredStringMap where FilteredViewName = 'FilteredContact' and AttributeName = 'new_test'

    Just keep in mind that FilteredViews gives you the name and id of the optionset/lookups etc. So when in doubt about the relationship or the derived fileds, check the view defenition. you can find the base tables or the filteredview that contains the actual tables and views. Only System Administrators on local database could view the definitions of views.



    Saturday, October 4, 2014 10:44 AM