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