A fast way to count the number of rows in each table in SQL Server

Time:2021-5-12

We all know that the aggregate function count() can count the number of rows in a table. If you need to count the number of rows of each table in the database (DBA may have this requirement), you must generate a dynamic SQL statement for each table and execute it with count() function to get the result. I used to see a good solution on the Internet. I forgot the source. Write it down and share it.

This method makes use of the rows field provided by the sysindexes system table. The rows field records the number of rows at the data level of the index. The solution code is as follows:

Copy codeThe code is as follows:
select schema_name(t.schema_id) as [Schema], t.name as TableName,i.rows as [RowCount]

from sys.tables as t, sysindexes as i

where t.object_id = i.id and i.indid <=1

This method connects the sys. Tables view to find the table name and schema_ ID, and then through the schema_ The name function gets the schema name of the table. The filter condition i.ind < = 1 only selects clustered index or heap, and each table has at least one heap or clustered index, so as to ensure that one row is returned for each table. Here are some of the results returned by running this query in my AdventureWorks database:

Copy codeThe code is as follows:
Schema                  TableName              RowCount
——————– ——————– ———–
Sales                      Store                     701
Production              ProductPhoto          101
Production              ProductProductPhoto  504
Sales                      StoreContact          753
Person                    Address                 19614
Production              ProductReview         4
Production              TransactionHistory   113443
Person                   AddressType            6

The advantages of this method are as follows

1. It runs very fast.
2. Because the user table is not accessed, the lock will not be placed on the user table, and the performance of the user table will not be affected.
3. The query can be written as subquery, CTE or view, which can be used in combination with other queries.