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:
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:
——————– ——————– ———–
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.