Dynamics CRM 2011 SQL MetadataSchema Primary Name Attribute

Question

I am trying to create a custom Audit summary report based on a date range that needs to be emailed nightly. I've got most of it working, but need some help with getting the primary name/field/attribute for a referenced entity. I noticed in the Audit view for a referenced entity, the data is stored like 'systemuser;'. What I would like to do is grab the Primary Field (Primary Name, Primary Attribute, whatever it's called) to display on the report. Does anyone know how to find the Primary Name attribute for an entity using the MetadataSchema views in the CRM SQL database? I have found the Primary Key field by looking at the MetadataSchema.Attribute.IsPKAttribute field, but I don't see anything for the Primary Name field.

Also, I am grabbing the current values from the entities if there are no following audit entries. For the lookup fields (like owner or customer) how can I tell from the Metadata what field stores the ObjectTypeCode? For example, if I was looking up the customer on a sales order, I know I can look at CustomerIdType field to find the ObjectTypeCode, but I need to find that the field is called CustomerIdType from the metadata.

If anyone has any good references on the Metadata from the SQL side of CRM, I would greatly appreciate it.

Answer 1

1. SQL query to get primary fields for all entities

   SELECT e.Name as 'entity', a.Name as 'primary field'
   FROM [dbo].EntityView e
   left join [dbo].AttributeView a on e.EntityId = a.EntityId
   where (a.DisplayMask & 256) > 0  --256 is for primary field
   order by e.name
   

2. There are two cases to get object type code of lookup

   • append Type to field name (i.e. CustomerIdType)

   • if above is not available, get it from AttributeMetadata

     SELECT ReferencedEntityObjectTypeCode
     FROM [Discworld_MSCRM].[dbo].[AttributeView]
     where name = '<field name>' and entityid = '<entity id>'
     

   I'm not sure what exact rules are for Type fields to exist