[mssql] How To Get Table Row Counts Quickly And Painlessly

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 o.id = i.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 o.OBJECT_ID = i.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps 
    ON ddps.OBJECT_ID = i.OBJECT_ID AND 
       ddps.index_id = i.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!