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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook


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?