SQL Server: Count Rows in Tables and its Size

In this post we will see how to count rows in all the tables of a database using SQL Server. A couple of months ago I had written a similar query Count Rows in all the Tables of a SQL Server Database using DBCC UPDATEUSAGE and the undocumented stored procedure sp_msForEachTable. However the rows returned using this approach could be inaccurate at times.

I consulted my DBA friend Yogesh who told me about another approach which works accurately and counts both the rows as well as space taken by a table. The code shown here works well for SQL Server 2005 and above.

Here’s the same query for you to try out:

USE ADVENTUREWORKS
GO
-- Count All Rows and Size of Table by SQLServerCurry.com
SELECT
TableName = obj.name,
TotalRows = prt.rows,
[SpaceUsed(KB)] = SUM(alloc.used_pages)*8
FROM sys.objects obj
JOIN sys.indexes idx on obj.object_id = idx.object_id
JOIN sys.partitions prt on obj.object_id = prt.object_id
JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
WHERE
obj.type = 'U' AND idx.index_id IN (0, 1)
GROUP BY obj.name, prt.rows
ORDER BY TableName

As you can see, we are using the sys.partitions catalog view which contains a row for each partition of all the tables and most types of indexes in the database. We are also using the sys.allocation_units catalog view to calculate the number of total pages actually in use.

Index_id 0 and 1 are for Heap and Clustered indexes respectively. Object Type ‘U’ is for User-defined Tables

OUTPUT (Partial)