How to extract data from metadataSchema tables from CRM Dynamics Online

[Rebecca Starr]

We have a number of CRM Cloud instances. We need to pull the data from each instance into a SQL Server database, for a 3rd party to then extract and load into a data warehouse. The 3rd party has provided a list of tables that they wish to use, including system tables (belonging to metadataschema). We currently have an on-premise instance that I am using to map the jobs. But although I can see the tables in the database, I cannot see them through the Microsoft Dynamics 365 and CRM connector.

The reason for pulling this information is to grab lookup values for reference data. Here is a sample script that they sent me:

Is there a way to get this information I am assuming that becuase these are system tables, they are not readily available to select from

 

select distinct env.objecttypecode

, env.name

, stm.attributename

, stm.attributevalue

, stm.langid

, stm.value

, stm.displayorder -- select *

from dbo.stringmapbase stm

join metadataschema.entity env

on env.objecttypecode = stm.objecttypecode

where stm.langid = 1033

order by env.name

, stm.attributename

, stm.langid

, stm.displayorder