Row count for all the tables in a database with MSSQL

People often request reports or views that have everything. In the old days it was a few hundred or a few thousand of rows, today it's millions and billions of rows. Many of these reports go straight to the printer. I don't have to imagine, a customer mine recently did it: 3 million rows to be printed, without looking and checking that what he needed. He aborted the report after printing 5,000 pages.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

How to get the row count for all the tables in a database.

Microsoft SQL Server does a lot of processes in the background. Sometimes, actually quite often on very heavy workload, the background processes run behind schedule to a point where the background operation is not done. Updating index statistics and row counts are some of these background tasks. Microsoft provides the dbcc updateusage to updates its own stats.

  1. dbcc updateusage reports and corrects pages and row count inaccuracies in the various catalog views. This often leads to incorrect data from sp_spaceused system stored procedure.
  2. Get the row number from sys.sysindexes

Row count

USE sql911;
go
DBCC updateusage (0) WITH count_rows;
go
SELECT CAST(OBJECT_NAME(id) AS VARCHAR(40)) AS 'Table',
       ROWS AS 'Rows'
FROM sys.sysindexes
WHERE OBJECT_NAME(id) not like 'sys%' and indid = 1
ORDER BY 'Table';
go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table                                    Rows
---------------------------------------- -----------
numbers                                  0
Nums                                     1048577
ORG                                      10
projects                                 20
queue_messages_1977058079                0
queue_messages_2009058193                0
queue_messages_2041058307                0
sales02                                  32

(8 row(s) affected)
  • If nothing is returned then the usage/indexes statistics are up-to-date.
  • Line 3: dbcc updateusage (0) updates the current database.
  • Line 3: with count_rows means that the row count column is also updated.
  • You need to have admin privileges to the database.

SQL server 2000: use sysindexes instead of sys.sysindexes