Retrieve Option Set Metadata Via SQL

When working on projects, typically those that involve integration, several stakeholders may need to know the underlying integer values of option sets. Said stakeholders may be third parties or internal developers, who don't necessarily have access to view option set metadata in CRM. The task of providing this metadata can therefore fall to you, the CRM consultant, which can prove to be time consuming and a project bottleneck.

You'll move to your CRM solution, find the attribute and create an excel table for the requested option set to e-mail back to the stakeholder:

Speed things up

If you or better yet, the other stakeholder has SQL access, run the following script to generate a table of option set values and labels for a given option set:

The above code snippet retrieves metadata for the statuscode option set on the Account entity.

In the WHERE clause:

• statuscode can be replaced with the schema name of any option set
• Account can be replaced with the schema name of any entity

Note that the e.Name condition is not necessary if you are sure the option set attribute only exists on one entity. In this example however, we need to specify the entity because we know that statuscode exists on every entity in CRM.