Quick Method for counting the number of rows in each table in SQL Server

Quick Method for counting the number of rows in each table in SQL Server

This article mainly introduces how to calculate the number of rows in each table in SQL Server. This article does not use the traditional count () function because it is slow and occupies resources, this article describes another method. For more information, see

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:

The 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:

The Code is as follows:

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:

1. Fast Running.

2. 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.

 

3. You can write the query as a subquery, CTE, or view and use it with other queries.