Skip to content

Retrieve Relationships by SQL

Today, I have a task on perform bulk delete for all the accounts in a new cloned organization.

However, during perform bulk deletion, system is not able to delete the records due to relationship behavior of delete is set as restrict, which mean that the related records need to be delete before delete the account record.

Because of it is a on-premise CRM, I have use one of the script below to query out the relationships information:

SELECT PrimaryEntity = referenced.name, 
PrimaryEntityKey = referencedAttribute.Name, SecondaryEntity = referencing.Name, LookupField = referencingAttribute.Name,  RelationshipName = relationship.Name,
relationship.RelationshipType, -- 0 = many to many, 1 = 1 to many or many to 1
relationship.IsLogical,
relationship.CascadeDelete,
relationship.CascadeAssign,
relationship.CascadeShare,
relationship.CascadeUnShare,
relationship.CascadeMerge,
relationship.CascadeReparent,
relationship.CascadeLinkMask 
FROM dbo.RelationshipView relationship
JOIN dbo.EntityView referencing on relationship.ReferencingEntityId = referencing.EntityId
JOIN dbo.EntityView referenced on relationship.ReferencedEntityId = referenced.EntityId
JOIN dbo.AttributeView referencingAttribute on relationship.ReferencingAttributeId = referencingAttribute.AttributeId
JOIN dbo.AttributeView referencedAttribute on relationship.ReferencedAttributeId = referencedAttribute.AttributeId
WHERE relationship.IsCustomRelationship = 1
AND referenced.name = 'account'

The above script is filter the custom relationship as well as the entity account only.

Refer the Cascade Delete column and map with below table:

Cascade None0
Cascade All1
Remove Link2
Restrict3
Cascade Active4
Cascade User-Owned5

It may need to drill down for those entities to check if there is more deletions need to be perform.

The best is copy the query result and use an Excel file to filter out those needed information.

The above query is tested on Dynamics 365 v8.

For a brief understanding about CRM relationships, please refer blog post below:

https://softchief.com/2017/08/23/dynamics-365-crm-entity-relationships-understandings/

Leave a Reply

Your email address will not be published.