Wednesday, 15 April 2015

MS-CRM - Query to list the relationship between the Entities

In the recent past, I have been associated with multiple data migration projects. All of them had one thing common, the destination for all those projects was MS-CRM database. 

There were occasions when it was required to know the parent/child entity relationship before SSIS package for the migration could be designed. For this information, I used to bug CRM team of respective project every now and then, thanks to my exceptional remembering/recalling power and unfamiliarity with CRM Tool . :) 

CRM Team had enough of me one day and told me "So what you don't know CRM tool, entity relationship information can be extracted from CRM database as well." A

And it triggered me to write this script and believe me CRM Team was more happy than I was when I was done with it..

Rel.Name AS Relationname
   ,ParentEnt.Name AS ParentEntity
   ,ChildEnt.Name AS ChildEntity
   ,ParentAtr.Name AS ParentAttrbuite
   ,ChildAtr.Name AS ChildAttribute
FROM Relationship Rel
INNER JOIN Entity ParentEnt
ON Rel.ReferencedEntityId ParentEnt.EntityId
INNER JOIN Entity ChildEnt
ON Rel.ReferencingEntityId ChildEnt.EntityId
INNER JOIN Attribute ChildAtr
ON ChildAtr.AttributeId Rel.ReferencingAttributeId
INNER JOIN Attribute ParentAtr
ON ParentAtr.AttributeId Rel.ReferencedAttributeId
WHERE ChildEnt.Name 'customeraddress' ORDER BY ParentEntity

No comments:

Post a Comment