February 24, 2011

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.

count rows sql server

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

-- Count All Rows and Size of Table by SQLServerCurry.com
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
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)

SQL Server Count Rows and Size

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal


Gaurav said...

Thanks for useful information. How can we count efficiently if there is a filter criteria?

Madhivanan said...


What did you mean by filter criteria?