How to get total number of rows for all the tables in a particular database? In this post we are going to learn how we can get “total number of rows for all tables” in a database.
We will use the inbuilt system views of SQL Server to generate this list. Sometimes we need to pick some high record containing tables for some demo or any other purpose.
To get the desired output, first select the database for which you want to extract the list of tables with total number of rows as below;
USE AdventureWorks2012 GO
Now use the below query and have a look on its output;
SELECT SCHEMA_NAME(ST.schema_id) + '.' + ST.Name AS TableName, MAX(SP.rows) AS TotalRows FROM SYS.Tables ST INNER JOIN SYS.partitions SP on SP.object_id = ST.object_id GROUP BY SCHEMA_NAME(ST.schema_id) + '.' + ST.Name ORDER BY TotalRows DESC
SELECT SCHEMA_NAME(ST.schema_id) + '.' + ST.Name… More