In order for the rowcounts found in sysIndexes in SQL Server 2000 to be anywhere close to accurate, you need to use DBCC UPDATEUSAGE on the table you're trying to get the rowcount on. To get the rowcounts (and a lot of other information) for all tables in a database, you need to do something like this ...

--_______________________________________________________________________________________________________________________

/**********************************************************************************************************************

Purpose:

Returns a single result set similar to sp_Space used for all user tables at once.

Notes:

1. May be used as a view, stored procedure, or table-valued funtion.

2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

Revision History:

Rev 00 - 22 Jan 2007 - Jeff Moden

- Initital creation for SQL Server 2000

Rev 01 - 11 Mar 2007 - Jeff Moden

- Add automatic page size determination for future compliance

Rev 02 - 05 Jan 2008 - Jeff Moden

- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name

**********************************************************************************************************************/

--===== Ensure that all row counts, etc is up to snuff

-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should

-- execute the command below prior to retrieving from the view or UDF.

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS

--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more

SELECT DBName = DB_NAME(),

--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000

SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005

TableName = so.Name,

TableID = so.ID,

MinRowSize = MIN(si.MinLen),

MaxRowSize = MAX(si.XMaxLen),

ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

RowModCtr = MIN(si.RowModCtr),

HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

FROM dbo.SysObjects so,

dbo.SysIndexes si,

(--Derived table finds page size in KB according to system type

SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

FROM Master.dbo.spt_Values

WHERE Number = 1 --Identifies the primary row for the given type

AND Type = 'E' --Identifies row for system type

) pkb

WHERE si.ID = so.ID

AND si.IndID IN (0, --Table w/o Text or Image Data

1, --Table with clustered index

255) --Table w/ Text or Image Data

AND so.XType = 'U' --User Tables

AND PERMISSIONS(so.ID) 0

GROUP BY so.Name,

so.UID,

so.ID,

pkb.PageKB

ORDER BY ReservedKB DESC

Notice that there are no cursors or While loops... sp_MSForEachTable is nothing but the world's nastiest cursor behind the scenes. The code above also has the advantage of returning all the data in a single result set.

So far as running SELECT COUNT(*) against a million row table goes.... try it out... I believe you'll be surprised at how quick it actually is and it's accurate at the time of the run...

Here's a million row test table...

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 100,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (SomeID)

And here's the test to run against it...

SELECT COUNT(*) FROM dbo.JBMTest