Dynamic CRM 2011 SQL MetadataSchema Primary Name Attribute

Question

I am trying to create a custom audit audit report based on a date range that needs to be emailed at night. I have most of its work, but you need help getting the primary name / field / attribute for the object referenced. I noticed that in the Audit view for the reference object, the data is stored as "systemuser;". What I would like to do is to capture the primary field (primary name, primary attribute, whatever it is called) for display in the report. Does anyone know how to find the primary name attribute for an object using MetadataSchema views in a CRM SQL database? I found the Primary Key field by looking at the MetadataSchema.Attribute.IsPKAttribute field, but I see nothing in the Primary Name field.

In addition, I grab current values ​​from entities if there are no following audit records. For search fields (for example, owner or client), how can I find out from metadata in which field ObjectTypeCode is stored? For example, if I searched for a customer by customer order, I know that I can find the CustomerIdType field to search for ObjectTypeCode, but I need to find that this field is called CustomerIdType from the metadata.

If anyone has good metadata references from the SQL side of CRM, I would really appreciate it.

Answer 1

• SQL query to get primary fields for all objects

  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 

• There are two cases to get the code of the type of the search object.

  • add Type to the field name (i.e. CustomerId Type )

  • If the 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 exist for fields like