A quick way to count each table row in SQL Server _mssql

We all know the number of rows that can be counted with the aggregate function count (). The count () function must generate a dynamic SQL statement for each table and execute it to get results if you need to count the number of rows per table for the database (which the DBA might need). Previously seen on the internet has a very good solution, forget the source, write down and share.

This method utilizes 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 code for the workaround is as follows:

Copy Code code 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, finds the table name and schema_id, and then obtains the schema name of the table through the Schema_name function. Filter Criteria I.indid <=1 Select only the clustered index or heap, each table has at least one heap, or a clustered index, ensuring that a row is returned for each table. The following are some of the results returned by running this query in my AdventureWorks database:

Copy Code code 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:

1. Run very fast.
2. Because the user table is not accessed, locks are not placed on the user table and the performance of the user table is not affected.
3. The query can be written as a subquery, a CTE, or a view, in conjunction with other queries.