Stone Coast Web Design Blog

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

Here is another method of doing this:

CREATE  Procedure dbo.RecordCountForAllTables 
AS
SELECT 
      'Table Name'=convert(char(25),t.TABLE_NAME),
      'Record Count'=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
Exec RecordCountForAllTables