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)
Thursday, September 3, 2009
Find row count of all the tables in a Database
Labels:
Interview Questions,
SQL Information,
SQL Queries
Subscribe to:
Post Comments (Atom)
2 comments:
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
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