{"id":45,"date":"2019-01-04T22:00:53","date_gmt":"2019-01-04T14:00:53","guid":{"rendered":"https:\/\/just-a-web.com\/blog\/?p=45"},"modified":"2020-11-03T22:06:13","modified_gmt":"2020-11-03T14:06:13","slug":"retrieve-relationships-by-sql","status":"publish","type":"post","link":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/","title":{"rendered":"Retrieve Relationships by SQL"},"content":{"rendered":"\n

Today, I have a task on perform bulk delete for all the accounts in a new cloned organization.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Because of it is a on-premise CRM, I have use one of the script below to query out the relationships information:<\/p>\n\n\n\n

SELECT PrimaryEntity = referenced.name, \r\nPrimaryEntityKey = referencedAttribute.Name, SecondaryEntity = referencing.Name, LookupField = referencingAttribute.Name,  RelationshipName = relationship.Name,\r\nrelationship.RelationshipType, -- 0 = many to many, 1 = 1 to many or many to 1\r\nrelationship.IsLogical,\r\nrelationship.CascadeDelete,\r\nrelationship.CascadeAssign,\r\nrelationship.CascadeShare,\r\nrelationship.CascadeUnShare,\r\nrelationship.CascadeMerge,\r\nrelationship.CascadeReparent,\r\nrelationship.CascadeLinkMask \r\nFROM dbo.RelationshipView relationship\r\nJOIN dbo.EntityView referencing on relationship.ReferencingEntityId = referencing.EntityId\r\nJOIN dbo.EntityView referenced on relationship.ReferencedEntityId = referenced.EntityId\r\nJOIN dbo.AttributeView referencingAttribute on relationship.ReferencingAttributeId = referencingAttribute.AttributeId\r\nJOIN dbo.AttributeView referencedAttribute on relationship.ReferencedAttributeId = referencedAttribute.AttributeId\r\nWHERE relationship.IsCustomRelationship = 1\r\nAND referenced.name = 'account'<\/code><\/pre>\n\n\n\n
The above script is filter the custom relationship as well as the entity account only.<\/p>\n\n\n\n
Refer the Cascade Delete column and map with below table:<\/p>\n\n\n\n
Cascade None<\/td>0<\/td><\/tr>
Cascade All<\/td>1<\/td><\/tr>
Remove Link<\/td>2<\/td><\/tr>
Restrict<\/td>3<\/td><\/tr>
Cascade Active<\/td>4<\/td><\/tr>
Cascade User-Owned<\/td>5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
It may need to drill down for those entities to check if there is more deletions need to be perform.<\/p>\n\n\n\n
The best is copy the query result and use an Excel file to filter out those needed information.<\/p>\n\n\n\n
The above query is tested on Dynamics 365 v8.<\/p>\n\n\n\n
For a brief understanding about CRM relationships, please refer blog post below:<\/p>\n\n\n\n
https:\/\/softchief.com\/2017\/08\/23\/dynamics-365-crm-entity-relationships-understandings\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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… Read More »Retrieve Relationships by SQL<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[1,10],"tags":[25,22],"class_list":["post-45","post","type-post","status-publish","format-standard","hentry","category-dynamics","category-sql","tag-relationships","tag-sql"],"yoast_head":"\nRetrieve Relationships by SQL - Dynamics Blog<\/title>\n<meta name=\"description\" content=\"How to retrieve entity relationships using SQL statement for OnPremise CRM.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Retrieve Relationships by SQL - Dynamics Blog\" \/>\n<meta property=\"og:description\" content=\"How to retrieve entity relationships using SQL statement for OnPremise CRM.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Dynamics Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-04T14:00:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-03T14:06:13+00:00\" \/>\n<meta name=\"author\" content=\"hk.ang\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"hk.ang\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/\"},\"author\":{\"name\":\"hk.ang\",\"@id\":\"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af\"},\"headline\":\"Retrieve Relationships by SQL\",\"datePublished\":\"2019-01-04T14:00:53+00:00\",\"dateModified\":\"2020-11-03T14:06:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/\"},\"wordCount\":180,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af\"},\"keywords\":[\"relationships\",\"sql\"],\"articleSection\":[\"Dynamics\",\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/\",\"url\":\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/\",\"name\":\"Retrieve Relationships by SQL - Dynamics Blog\",\"isPartOf\":{\"@id\":\"https:\/\/just-a-web.com\/blog\/#website\"},\"datePublished\":\"2019-01-04T14:00:53+00:00\",\"dateModified\":\"2020-11-03T14:06:13+00:00\",\"description\":\"How to retrieve entity relationships using SQL statement for OnPremise CRM.\",\"breadcrumb\":{\"@id\":\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/just-a-web.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Retrieve Relationships by SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/just-a-web.com\/blog\/#website\",\"url\":\"https:\/\/just-a-web.com\/blog\/\",\"name\":\"Dynamics Blog\",\"description\":\"A blog that related to coding stuff\",\"publisher\":{\"@id\":\"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/just-a-web.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af\",\"name\":\"hk.ang\",\"logo\":{\"@id\":\"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/just-a-web.com\/blog\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Retrieve Relationships by SQL - Dynamics Blog","description":"How to retrieve entity relationships using SQL statement for OnPremise CRM.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/","og_locale":"en_US","og_type":"article","og_title":"Retrieve Relationships by SQL - Dynamics Blog","og_description":"How to retrieve entity relationships using SQL statement for OnPremise CRM.","og_url":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/","og_site_name":"Dynamics Blog","article_published_time":"2019-01-04T14:00:53+00:00","article_modified_time":"2020-11-03T14:06:13+00:00","author":"hk.ang","twitter_card":"summary_large_image","twitter_misc":{"Written by":"hk.ang","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#article","isPartOf":{"@id":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/"},"author":{"name":"hk.ang","@id":"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af"},"headline":"Retrieve Relationships by SQL","datePublished":"2019-01-04T14:00:53+00:00","dateModified":"2020-11-03T14:06:13+00:00","mainEntityOfPage":{"@id":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/"},"wordCount":180,"commentCount":0,"publisher":{"@id":"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af"},"keywords":["relationships","sql"],"articleSection":["Dynamics","SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/","url":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/","name":"Retrieve Relationships by SQL - Dynamics Blog","isPartOf":{"@id":"https:\/\/just-a-web.com\/blog\/#website"},"datePublished":"2019-01-04T14:00:53+00:00","dateModified":"2020-11-03T14:06:13+00:00","description":"How to retrieve entity relationships using SQL statement for OnPremise CRM.","breadcrumb":{"@id":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/just-a-web.com\/blog\/2019\/01\/04\/retrieve-relationships-by-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/just-a-web.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Retrieve Relationships by SQL"}]},{"@type":"WebSite","@id":"https:\/\/just-a-web.com\/blog\/#website","url":"https:\/\/just-a-web.com\/blog\/","name":"Dynamics Blog","description":"A blog that related to coding stuff","publisher":{"@id":"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/just-a-web.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/7c5fde715b55adf97070f3506709b0af","name":"hk.ang","logo":{"@id":"https:\/\/just-a-web.com\/blog\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/just-a-web.com\/blog"]}]}},"_links":{"self":[{"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/posts\/45","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/comments?post=45"}],"version-history":[{"count":1,"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/posts\/45\/revisions"}],"predecessor-version":[{"id":46,"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/posts\/45\/revisions\/46"}],"wp:attachment":[{"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/media?parent=45"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/categories?post=45"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/just-a-web.com\/blog\/wp-json\/wp\/v2\/tags?post=45"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}