How to get row count of every table in a SQL Azure Database

Getting rowcount of a table using the count() function is not a good idea as it would take too long for a large table.  Worse, if you want to get the rowcount of every table in a database. Thankfully there is a dynamic view sys.dm_db_partition_stats that has the rowcounts readily available.

SELECT as TableName, ddps.row_count as [RowCount]
FROM sys.objects so
JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID
JOIN sys.dm_db_partition_stats AS ddps
       ON si.OBJECT_ID = ddps.OBJECT_ID  AND si.index_id = ddps.index_id
WHERE si.index_id < 2  AND so.is_ms_shipped = 0
ORDER BY ddps.row_count DESC

Hope this is usefull!


Anonymous said...

Great thanks :)

Unknown said...

Nice Article !

Really this will help to people of SQL Server Community.
I have also prepared small note on this, Script to find row size and count of the SQL Server Table.

Post a comment

Please let me know if you find it useful!