Find row count of all the tables in a Database | SansSQL

Thursday, September 3, 2009

Find row count of all the tables in a Database

Here is a quick and easy way to find the row count of all the tables in a database.
Before getting the row count, you have to update the usage of your database.

/*This Query works for SQL 2000, SQL 2005 and SQL 2008*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select object_name(id) as [Table Name],rowcnt as [RowCount] from sysindexes
where indid < 2 AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
Order by object_name(id)

/*This Query works for SQL 2005 and SQL 2008 as it uses DMV*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select Distinct Object_Name(object_id) as [Table Name], row_count as [RowCount] from sys.dm_db_partition_stats where OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
Order by Object_Name(object_id)

2 comments:

Anonymous said...

That's a pretty quick and dirty way to get the row counts, but just
realize that it's an estimate based on the last statistics (at least
from what I remember.)

Try this:


Get the counts for all the tables using your technique
Update the statistics
Get the counts for all the tables using your technique - verify that
everything is the same
Insert 1 row in one table
Remove 1 row in another table
Get the counts for all the tables using your technique again and
compare the counts for those two tables
update the statistics
Get the counts for all the tables using your technique again and
compare the counts for those two tables


I'm guessing you'll see different results based on when the stats were
updated...but still the results will be close and it does help for
some situations.


-Eric Isaacs

Sandesh Segu said...

Hi Eric,

I don't see any difference when i followed the steps you suggested.
Anyways thanks for your valuable sugestions and keep reading my blog :)

Regsrds,
Sandesh Segu

Post a Comment