Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Extract CRM Metadata RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    I am not a CRM guy but I have been working with SQL for the past 20years, I know that  you can extract the CRM metadata by using SQL views.

    check the second view

    https://community.dynamics.com/crm/b/crmjimwang/archive/2009/03/27/get-entity-47-attribute-39-s-display-name-from-crm-database

    I have legacy data that have been extracted into SQL tables, now I am going to use SSIS and KingswaysSoft the inject the legacy data into CRM2015 or 2016, I need to provide the team a mapping excel file so that I can create my SSIS packages, what I need is to extract the CRM metadata with the mentioned views

    1- First view: extract all Entities    (SELECT * FROM EntityView )  Above link

    2- 2nd View: Extract all Attributes (SELECT * FROM AttributeView ) above link

    3- 3rd View: extract ONLY the entities that are been used (SELECT * FROM ????)

    4- 4th View: extract ONLY the Attributes that are been used (SELECT FROM ????)

    the link above will helps me in the first 2 views, but I am looking for the other 2 views, why?

    for example the Entity "Contact" has 207 Attributes (Fields) but only 12 of them are been used in the application frontend.

    Thanks

    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s). http://sqldataside.blogspot.ca/ (SQL Tabular + PowerShell)

    Saturday, February 18, 2017 3:08 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You'll need to parse the respective form and view definitions, which are both in xml format.

    The form definitions are stored in the formxml column of the systemform entity, and for the views the main information is the columnsetxml column of the savedquery entity. You can join these tables to EntityView on the ObjectTypeCode field

    You can find what entities are in use from the sitemap, though I'd consider this to be incomplete as many entities may be viewed via subgrids or navigation items on another form (also identifiable via the formxml)

    Another option is to check which entities / attributes have data in them. You could use a cursor to iterate through these

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Monday, February 20, 2017 9:57 AM
    Moderator