Tag : database information


Get total number of rows for all tables

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

OR

SELECT SCHEMA_NAME(ST.schema_id) + '.' + ST.Name 
More