SQL Server 2005 Table Row Counts... One Select Statement, No Table Scan

A big change that occurred with SQL Server 2005 (from 2000) is that the system table information is always in sync with the actual tables.

When the system tables get out of sync in SQL Server 2000 the following SQL statement must be run to synchronize them.

dbcc updateusage(0)
GO 

According to the MSDN site on DBCC UPDATEUSAGE (Transact-SQL) contains this little bit of information:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Googling for information on getting table counts without a row scan yields several posts on the issue, but they involve creating functions and needlessly complicating a simple SQL statement.

There are 2 possible solutions. 1) Common Table Expressions and 2) Use a join. The first option looks like this:

WITH CTETableRowCount
AS
(
 SELECT 
   rows rowCnt
 , ID ObjectID 
 --, si.*
        FROM sysindexes si
        WHERE indid < 2
)
SELECT 
  so.[name]
, cte.[rowCnt] as [Row Count]
--, so.*
FROM sysobjects so LEFT JOIN CTETableRowCount cte ON so.id=cte.ObjectID
WHERE type='U' and so.name != 'dtproperties'
ORDER BY so.[name]

This works and is reliable, but a bit overkill. There is nothing in the Common Table Expression that really requires it to be like that and in fact just adds lines to the query with no benefit The above query can be simplified to the following:

SELECT 
   so.[name]
 , si.[rows] as [Row Count]
 --, so.*
FROM sysobjects so LEFT JOIN sysindexes si ON so.id=si.ID
WHERE (type='U') and (so.name != 'dtproperties') and (si.indid < 2)
ORDER BY so.[name]

This is probably the best solution to the issue.