Search code examples
sql-serverdynamics-crmcrmmicrosoft-dynamicsdynamics-crm-2015

MSD CRM: Get the count of records of all entities in CRM

I am working on to get the record count of every entity available in the CRM. I have seen so many solutions are available on the internet But I have searched in the database(As we have on-prem) and found one table called 'RecordCountSnapshot' has the count(and answer to my question). I am wondering can we query that table somehow and get the count.

I have tried using OData Query builder, I am able to prepare a query but unable to get the result.

Query:

enter image description here

Result:

enter image description here

We are using CRM 2015 on-prem version.

Solution

  • Go to Settings -> Customizations -> Developer Resources -> Service Endpoints -> Organization Data Service

    Open by clicking /XRMServices/2011/OrganizationData.svc/, it is missing the definition for RecordCountSnapshot. That means this entity is not serviceable by OData. Even if you modify the other OData query url to use RecordCountSnapshotSet you will get 'Not found' error. (I tried in CRM REST builder)

    1) As you are in Onpremise, You can use this query:

    SELECT TOP 1000 [Count]
,[RecordCountSnapshotId]
,entityview.ObjectTypeCode, Name
FROM [YOURCRM_MSCRM].[dbo].[RecordCountSnapshot] , EntityView 
where entityview.ObjectTypeCode = RecordCountSnapshot.ObjectTypeCode
and count > 0 order by count desc

    2) In Odata Query Designer, you have statistics tab. Use it to get the records count.