Count Rows in all the Tables of a SQL Server Database

To get an accurate value of all the rows in a SQL Server table, use DBCC UPDATEUSAGE. DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. 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

USE AdventureWorks
GO
DECLARE @DynSQL NVARCHAR(255) 
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
EXEC(@DynSQL) 

EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1) as TableName,
COUNT(*) as NumberOfRows FROM ?'

OUTPUT (Partial)

COUNT ROWS

at

3 comments:

  1. AnonymousApril 29, 2010 at 9:40 AM

    Suggestion for a new Blog Title: "How to kill larges Databases 101"

    ReplyDelete
  2. John DavidMay 2, 2010 at 11:19 PM

    Hey..i am also looking for the same topic.
    thanks for sharing with us all.

    ReplyDelete
  3. MadhivananJuly 6, 2010 at 12:16 AM

    Another method is

    select object_name(id) as table_name, rows from sys.sysindexes
    where indid<2
    order by table_name

    ReplyDelete