Posted on

Retrieve and Export Microsoft Dynamics CRM 2011 Database Schema and MetaData to Excel

It’s often required to retrieve Microsoft Dynamics CRM 2011 Database Schema and also to browse, view and edit Dynamics CRM MetaData including each entity, attributes (fields – database columns) and entities relationship (1 to Many, Many to 1 and Many to Many) then export this to an excel sheet or other forms of documentation.

To do this, you have several tools and ways. I’ll quickly list some of them in this post:

Option 1: Use the Microsoft Dynamics CRM SDK MetaData Browser tool. This comes as part of the SDK under the Tools folder when you unpack the latest CRM SDK. To use this tool, you need to import the managed solution that you can find in the MetaData browser folder and open the managed solution after importing then click on configuration.

Option 2: Use the Dynamics CRM Tools MetaData Browser and exporter to Excel available at CodePlex: http://crm2011metabrowser.codeplex.com/

This tool allows you to export to excel but it doesn’t allow exporting the whole database to excel but rather entity by entity (as far as I can see).

Option 3: It’s my preferred method which is using the old trick of writing an SQL Query that returns Dynamics CRM 2011 database schema. You can obviously change in this query to add or remove more database columns in the returned results or getting the result in a different format.

Here is the SQL query:

SELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label 
AS EntityDisplayName, AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label 
AS AttributeDisplayName,AttributeTypes.Description as Type, AttributeTypes.XmlType,AttributeView.Length,Accuracy,IsCustomEntity,
IsMappable,EntityView.IsCustomizable,
EntityView.IsRenameable,IsActivity,AttributeView.IsCustomField 
FROM    LocalizedLabelView AS LocalizedLabelView_2
INNER JOIN  AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
RIGHT OUTER JOIN  EntityView INNER JOIN LocalizedLabelView AS LocalizedLabelView_1
ON EntityView.EntityId = LocalizedLabelView_1.ObjectId 
ON AttributeView.EntityId = EntityView.EntityId
INNER JOIN attributetypes on AttributeView.AttributeTypeId = AttributeTypes.AttributeTypeId
WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName' 
AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
AND LocalizedLabelView_1.LanguageId = '1033' 
AND LocalizedLabelView_2.LanguageId = '1033'
ORDER BY EntityDisplayName, AttributeName

 

Please comment below if you have done a better/different query or if you are having issues with this one.

Thanks!

Mohamed Mostafa