To get an accurate value of all the rows in a SQL Server table, use DBCC UPDATEUSAGE. Here’s a query that first uses DBCC UPDATEUSAGE and then count all the rows in all the tables of a database using the undocumented stored procedure sp_msForEachTable.
DECLARE @DynSQL NVARCHAR(255)
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@DynSQL)
EXEC sp_msForEachTable'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
