Quick statistics on the number of rows in each table of SQL Server

We all know that using the aggregate function count () can calculate the number of rows in the table. To count the number of rows of each table in the database (DBA may have this requirement), use the count () function to generate a dynamic SQL statement for each table and execute it to obtain the result. I used to see a good solution on the internet. I forgot the source and wrote it down to share it.

This method uses 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:

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 to the sys. tables view, finds the table name and schema_id, and obtains the schema name of the table through the schema_name function. Filtering condition I. indid <= 1: Only clustered indexes or heaps are selected. Each table has at least one heap or clustered index, so that a row is returned for each table. The following are some results returned by running this query in my AdventureWorks database:

Schema TableName RowCount
------------------
Sales Store 701
Production ProductPhoto 101
Production ProductProductPhoto 504
Sale StoreContact 753
Person Address 19614
Production ProductReview 4
Production TransactionHistory 113443
Person AddressType 6

This method has the following advantages:

The running speed is very fast.
Because the user table is not accessed, no locks are placed on the user table and the performance of the user table is not affected.
You can write the query as a subquery, CTE, or view and use it with other queries.