Attribute and picklist / option set metadata in CRM 2011

A customer requested a list of attribute names and their associated label for a particular CRM entity. I just used a SQL query to get the info and cut-and-pasted into Excel. Easy…

SELECT DISTINCT a.Name, l.Label
FROM MetadataSchema.Attribute a
INNER JOIN MetadataSchema.Entity e
ON a.EntityId = e.EntityId
AND YEAR(e.OverwriteTime) = 1900
INNER JOIN MetadataSchema.LocalizedLabel l
ON a.AttributeId = l.ObjectId
AND l.ObjectColumnName='DisplayName'
AND l.LanguageId = 1033
WHERE e.Name = 'opportunity'

Then they asked for a list of all picklist values and labels on the system. A slightly more complex query for that but it was fun figuring out the MetadataSchema tables.

SELECT DISTINCT e.Name as Entity, a.Name as Attribute, pl.Value, l.Label
FROM MetadataSchema.Attribute a
INNER JOIN MetadataSchema.Entity e
ON a.EntityId = e.EntityId
AND YEAR(e.OverwriteTime) = 1900
INNER JOIN MetadataSchema.AttributeTypes t
ON a.AttributeTypeId = t.AttributeTypeId
AND t.Description = 'picklist'
INNER JOIN MetadataSchema.AttributePicklistValue pl
ON a.OptionSetId = pl.OptionSetId
INNER JOIN MetadataSchema.LocalizedLabel l
ON pl.AttributePicklistValueId = l.ObjectId
ORDER BY e.Name, a.Name

New: Full entity metadata export from SQL

SELECT
  a.AttributeId,
  at.Description as AttributeType,
  a.Name,
  a.Length,
  l.Label,
  d.Label as Description
FROM MetadataSchema.Attribute a
INNER JOIN MetadataSchema.AttributeTypes at
  ON  a.AttributeTypeId = at.AttributeTypeId
INNER JOIN MetadataSchema.LocalizedLabel l
  ON  a.AttributeId = l.ObjectId
  AND YEAR(l.OverwriteTime) = 1900
  AND l.ObjectColumnName='DisplayName'
LEFT JOIN MetadataSchema.LocalizedLabel d
  ON  a.AttributeId = d.ObjectId
  AND YEAR(d.OverwriteTime) = 1900
  AND d.ObjectColumnName='Description'
WHERE a.EntityId = (SELECT EntityId FROM MetadataSchema.Entity WHERE Name='Account')
  AND YEAR(a.OverwriteTime) = 1900
ORDER BY l.Label

Posted by petecrm2011 on July 19, 2011

https://petecrm2011.wordpress.com/2011/07/19/attribute-and-picklist-option-set-metadata-in-crm-2011/