Getting Table Row Counts in Sql 2005

On September 6, 2009, in SQL, by Anuj Gakhar

I came across a nice tip while reading this article. If you want to get the number of rows in a table , you would normally do a select count(*) from tbl query, which does a table scan and can be a little time consuming query if the table has millions of rows.

This little query gives you all the counts for every table in your database, pretty quickly and instantly.

-- Shows all user tables and row counts for the current database
-- Remove is_ms_shipped = 0 check to include system objects
-- i.index_id < 2 indicates clustered index (1) or hash table (0)
SELECT o.name,
 ddps.row_count
FROM sys.indexes AS i
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
 AND i.index_id = ddps.index_id
WHERE i.index_id < 2
 AND o.is_ms_shipped = 0
ORDER BY o.NAME
Tagged with:  

2 Responses to Getting Table Row Counts in Sql 2005

  1. Fred M. says:

    I find that really helpful and it does save a lot of time. Thanks!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 446 other subscribers

© 2011 Anuj Gakhar
%d bloggers like this: