Wednesday, August 31, 2011

MSCRM – Relationships review helper script

Here is a tiny sample script to see all MS CRM relationships in one place and with nice attribute/entities names:

SELECT    r.Name as "RefName", e1.Name as "fromEntity", a1.Name as "fromAttr", e2.Name as "toEntity", a2.Name as "toAttr", 
        r.RelationshipType, r.IsLogical, r.CascadeDelete, r.CascadeAssign, r.CascadeShare,
        r.IsCustomRelationship
    FROM  [dbo].[Relationship] r
    JOIN  [dbo].[Entity] e1 ON e1.EntityId = r.ReferencingEntityId
    JOIN [dbo].[Entity] e2 ON e2.EntityId = r.ReferencedEntityId
    JOIN dbo.Attribute a1 ON r.ReferencingAttributeId = a1.AttributeId
    JOIN dbo.Attribute a2 ON r.ReferencedAttributeId = a2.AttributeId
    
    WHERE e1.EntityId IN (SELECT EntityId
        FROM [dbo].[Entity]
        WHERE Name in 
            ('Lead', 'V2_contract', 'Account', 'Email', 'V2_incidenthistory', 
            'SystemUserLicenses', 'QuoteClose', 'BulkOperation', 'V2_technicalspec'))
            OR 
            e2.EntityId IN (SELECT EntityId
        FROM [dbo].[Entity]
        WHERE Name in 
            ('Lead', 'V2_contract', 'Account', 'Email', 'V2_incidenthistory', 
            'SystemUserLicenses', 'QuoteClose', 'BulkOperation', 'V2_technicalspec'))
    ORDER BY "fromEntity", "fromAttr"
 

Change the entity names in the filter to what you need or delete it altogether. To be executed on the MSCRM metabase DB. Hope might save someone a minute :).

No comments: