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:

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)

SQL Server Count Rows and Size


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

2 Responses to "SQL Server: Count Rows in Tables and its Size"
  1. Gaurav said...
    February 27, 2011 at 12:34 PM

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

  2. Madhivanan said...
    February 27, 2011 at 10:54 PM

    Gaurav,

    What did you mean by filter criteria?

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions