of the
Want to count all records in all Dataverse tables like a boss? Say no mo.
- Make sure TDS endpoint is enabled using Manage feature settings.
- Connect to your Dataverse as described in Use SQL to query data (Microsoft Dataverse)
- We cannot use some of the features including loops (see How Dataverse SQL Differs from Transact-SQL) so let’s build that SQL:
SELECT
CONCAT(
'SELECT ''', name, ''' AS TableName,
COUNT_BIG(1) AS CountOfRows FROM ',
name,
' UNION')
FROM
sys.tables
WHERE
TYPE = 'U'
AND SCHEMA_ID = 1
- Copy all results (Ctrl-A), open New Query window (Ctrl-N), paste (Ctrl-V)
- Scroll to the end, remove the last word
UNIONand addORDER BY 2 DESCline - Execute
- You are welcome
Note: the execution may timeout if your Dataverse is of any decent size. Use trial/error approach to figure out executable chunks – you probably know your largest tables anyway.
You could use the Fast Record Counter in XRMToolbox by Ivan Ficko to get the same, yes?
https://www.xrmtoolbox.com/plugins/Fic.XTB.FastRecordCounter/
Super easy to use and quick! Thank you for the recommendation!
Not sure if the XRMToolbox can be downloaded to the production server. The SQL approach is pretty good.
I have created a browser-based tool to count total records for given table. https://AshishVishwakarma.com/DataverseRowsCounter/ Please have a look.
Does anyone have a similar script to do the same report for the number of columns?
In the SQL approach make sure, it support only 200 literal at once.
you have to split your query if it has more than 200 unions.