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.