Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How To Get Table Row Counts Quickly And Painlessly

By Kendal Van Dyke, (first published: 2009/09/02)

At some point in time we've all had to find out how many rows are in a table. The first answer you'll usually get when you ask someone how to do it is select count(*) from [table], however there are two problems with this approach: First, a table scan is required to figure out the answer; do a count(*) against a million row table and you're looking at hundreds of thousands of reads (and likely several minutes waiting for the result). Second, the count(*) method doesn't work well if you want to know how many rows are in every table in your database.

It just so happens there's a system function in SQL Server that can help solve both of these problems: sp_spaceused (BOL entry here) . When run without any parameters it returns usage information about the current database its being run in; when provided a specific object name (e.g. a table name) it returns the number of rows along with the amount of space used by\allocated for the table and its indexes. Looking under the covers of sp_spaceused reveals that the rowcount information is coming from the sysindexes table on SQL 2000 and the sys.dm_db_partition_stats DMV on SQL 2005\2008. Since the counts are coming from system objects there's no table scan involved - Problem #1 solved!

To solve problem #2 you could use a cursor to iterate through all tables (or the undocumented stored procedure sp_foreachtable), calling sp_spaceused for each table and storing the output in a temporary table...or just query the system objects directly.

Row Counts Using sysindexes
If you're using SQL 2000 you'll need to use sysindexes like so:

-- Shows all user tables and row counts for the current database 
-- Remove OBJECTPROPERTY function call to include system objects
SELECT o.NAME,
i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2
AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

Row Counts Using DMVs
If you're using SQL 2005 or 2008 querying sysindexes will still work but Microsoft advises that sysindexes may be removed in a future version of SQL Server so as a good practice you should use the DMVs instead, like so:

-- 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

Are The Counts Accurate?
Some system objects are only as accurate as the current statistics and occasionally statistics get outdated and need to be refreshed. Fortunately row count information in sysindexes\DMVs does not depend on updated statistics. To put this to the test I disabled the Auto Update Statistics option on a database that sees several thousand updates each day. After several days I compared the counts returned by the select count(*) method and the system objects and they matched perfectly.

Obviously you'll need to revert to the select count(*) method if you need to filter out rows (using a where clause), but for unfiltered row count information there's no excuses not to use the system objects!

Total article views: 50731 | Views in the last 30 days: 108
 
Related Articles
FORUM

object_name(id) returning Null

sysindexes

FORUM

Noncluster at sysindexes

Noncluster at sysindexes

FORUM

Errors on SYSINDEXES

SQL2000, errors 8929,8928,8939,8965,8964 on SYSINDEXES

BLOG

SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These syst...

SCRIPT

Count of Objects by Database on a Server

I needed to get a count of objects by database on a server to assess which dbs are good candidates f...

Tags
row count    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones