Dynamics CRM - SQL query to get entity that lookup relates to

Question

Within the CRM database, where can I find the name of the entity that a lookup field relates to?

For example, I have an Account entity which contains two lookup fields;

• Account Contact, which points to the Contact entity
• Leased Equipment, which points to the Equipment entity

How can I get the name of the entity that each of the fields relates to?

I expected to see this in the MetadataSchema.AttributeLookupValue table, but this doesn't seem to be the right place.

Ideally, I'd like to write a query for this information. Something along the lines of:

SELECT
    AttributeName,
    AttributeTypeName,
    ReferencedEntityName
FROM 
    unknown.TableName

WHERE
    AttributeName IN ('new_AccountContact', 'new_LeasedEquipment')

-- Results:
-- AttributeName        | AttributeTypeName | ReferencedEntityName
-- new_AccountContact   | lookup            | Contact
-- new_LeasedEquipment  | lookup            | Equipment

Any help trying to achieve this would be appreciated, thanks.

Answer 1

The query below returns the primary entity, lookup field name, and the target entity type of the lookup field.

    SELECT e.Name 'primary entity' , a.LogicalName 'lookup field in primary entity' , a.ReferencedEntityObjectTypeCode , e2.LogicalName as 'target entity of the lookup field'
    FROM MetadataSchema.Attribute a inner join MetadataSchema.Entity e on a.EntityId = e.EntityId
                                    inner join MetadataSchema.Entity e2 on a.ReferencedEntityObjectTypeCode=e2.ObjectTypeCode
    WHERE  ReferencedEntityObjectTypeCode <>0
    AND e.name='account'