How to write SOQL query in Salesforce to get total row/record counts of each object along with the object name?

Question

SELECT qualifiedapiname
FROM EntityDefinition 
WHERE IsQueryable = true 
  AND IsRetrieveable = true 
  AND IsDeprecatedAndHidden = false
LIMIT 2000

Above query lists all SFDC standard/custom objects. But it also returns objects which have no (zero) records at all and I want to skip/filter them. How can we ?

Is there any field/property metadata in Salesforce's EntityDefinition that can tell if the object has any records in it or if its empty/blank, something like "IsBlank or IsEmpty= true/false" ?

Expected output am looking for is:

Object Name	RecordCount
Account	100
Lead	20
Opportunity	0

Looking at above output, I could filter Opportunity table further in the process since it doesn't have any records/rows yet.

Thanks!

Answer 1

Not sure if you need this information dynamically, or just periodically. You can get the information from the Rest API (use workbench or your favorite Chrome extension like Salesforce Inspector)

REST call

/services/data/v52.0/limits/recordCount? 

you can get either all record counts or specific objects...
Documentation
https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_record_count.htm

this gives you a JSON return that you can work with.

{
   "sObjects" : [ {
     "count" : 3,
     "name" : "Account"
   }, {
     "count" : 10,
     "name" : "Contact"
   } ]
}