Monday 4 August 2014

List Entity relationships in CRM 2011/2013 - Brain Dump 6

Cleaning up my inbox today when I found this SQL query, in short a quick way of listing all the relationships for a particular entity:

SELECT distinct (rel.name),ent.name,
        Case [CascadeDelete]
                         when 0 then 'None'
                         when 1 then 'All'
                         when 2 then 'Referential'
                         when 3 then 'Restrict'
                        end as CascadeDelete
      ,Case [CascadeAssign]
                         when 0 then 'None'
                         when 1 then 'All'
                         when 2 then 'Referential'
                         when 3 then 'Restrict'
                        end as CascadeAssign
      ,Case [CascadeShare]
                         when 0 then 'None'
                         when 1 then 'All'
                         when 2 then 'Referential'
                         when 3 then 'Restrict'
                        end as CascadeShare
      ,Case [CascadeUnShare]
                         when 0 then 'None'
                         when 1 then 'All'
                         when 2 then 'Referential'
                         when 3 then 'Restrict'
                        end as CascadeUnShare
      ,Case [CascadeReparent]
                         when 0 then 'None'
                         when 1 then 'All'
                         when 2 then 'Referential'
                         when 3 then 'Restrict'
                        end as CascadeReparent
      ,Case [CascadeMerge]
                         when 0 then 'None'
                         when 1 then 'All'
                         when 2 then 'Referential'
                         when 3 then 'Restrict'
                        end as CascadeMerge
       from MetadataSchema.Relationship rel
      
join  MetadataSchema.Entity ent on rel.ReferencedEntityId = ent.entityid
where rel.name like '%<entityname>%'
order by ent.Name

And the results:


No comments:

Post a Comment