CODEWITHSUNDEEP
sql-serverdynamics-crmdynamics-crm-2011microsoft-dynamics
devklick
devklick

Reputation: 2629

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

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;

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.

Upvotes: 1

Views: 3097

Answers (1)

Alessi
Alessi

Reputation: 769

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'

Upvotes: 3

Related Questions