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…

01	SELECT DISTINCT a.Name, l.Label

02	FROM MetadataSchema.Attribute a

03	INNER JOIN MetadataSchema.Entity e

04	ON a.EntityId = e.EntityId

05	AND YEAR(e.OverwriteTime) = 1900

06	INNER JOIN MetadataSchema.LocalizedLabel l

07	ON a.AttributeId = l.ObjectId

08	AND l.ObjectColumnName='DisplayName'

09	AND l.LanguageId = 1033

10	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.

01	SELECT DISTINCT e.Name as Entity, a.Name as Attribute, pl.Value, l.Label

02	FROM MetadataSchema.Attribute a

03	INNER JOIN MetadataSchema.Entity e

04	ON a.EntityId = e.EntityId

05	AND YEAR(e.OverwriteTime) = 1900

06	INNER JOIN MetadataSchema.AttributeTypes t

07	ON a.AttributeTypeId = t.AttributeTypeId

08	AND t.Description = 'picklist'

09	INNER JOIN MetadataSchema.AttributePicklistValue pl

10	ON a.OptionSetId = pl.OptionSetId

11	INNER JOIN MetadataSchema.LocalizedLabel l

12	ON pl.AttributePicklistValueId = l.ObjectId

13	ORDER BY e.Name, a.Name

New: Full entity metadata export from SQL

01

SELECT

02	a.AttributeId,

03	at.Description as AttributeType,

04

a.Name,

05

a.Length,

06

l.Label,

07	d.Label as Description

08	FROM MetadataSchema.Attribute a

09	INNER JOIN MetadataSchema.AttributeTypes at

10	ON  a.AttributeTypeId = at.AttributeTypeId

11	INNER JOIN MetadataSchema.LocalizedLabel l

12	ON  a.AttributeId = l.ObjectId

13	AND YEAR(l.OverwriteTime) = 1900

14	AND l.ObjectColumnName='DisplayName'

15	LEFT JOIN MetadataSchema.LocalizedLabel d

16	ON  a.AttributeId = d.ObjectId

17	AND YEAR(d.OverwriteTime) = 1900

18	AND d.ObjectColumnName='Description'

19	WHERE a.EntityId = (SELECT EntityId FROM MetadataSchema.Entity WHERE Name='Account')

20	AND YEAR(a.OverwriteTime) = 1900

21	ORDER BY l.Label

About these ads

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