Get Row Counts for all tables in SQL Server

Sometime you are required to validate the row counts for each tables in database. So getting a row counts for each tables either through using a loop on each table or go and get row counts one by one for each table in database, definitely that will be a lengthy process and take a lot of your time.

In this situation, the best way would be using any SQL Server scripts which provides a row counts information of tables.

In SQL Server there some System Dynamic Management View, and System catalog views that you can use for getting a row counts for each tables in SQL Server database.

Row counts for each tables using sys.dm_db_partition_stats

dm_db_partition_stats is a SQL Server dynamic management view which returns a page and row-count information for each partition in the current database.

Following T-SQL uses dmv dm_db_partition_stats and returns row counts for each tables in Database AdventureWorks2017.

USE AdventureWorks2017;
GO

SELECT
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) AS [TableName],
SUM(dmv.row_count) AS [RowCount]
FROM sys.objects AS obj
  INNER JOIN sys.dm_db_partition_stats AS dmv
  ON obj.object_id = dmv.object_id
WHERE obj.type = 'U'
  AND obj.is_ms_shipped = 0x0
  AND dmv.index_id in (0, 1)
GROUP BY obj.schema_id, obj.name
ORDER BY Obj.name

GO

You can see, it returns row counts for each table in database Adventureworks2017.

In case, if you want to see row counts for specific table then you can specify a table name in where condition as shown below.

Following T-SQL, returns row counts for table Address in database Adventureworks2017.

USE AdventureWorks2017;
GO

SELECT
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) AS [TableName],
SUM(dmv.row_count) AS [RowCount]
FROM sys.objects AS obj
   INNER JOIN sys.dm_db_partition_stats AS dmv
   ON obj.object_id = dmv.object_id
WHERE obj.type = 'U'
   AND obj.is_ms_shipped = 0x0
   AND dmv.index_id in (0, 1)
   AND obj.Name ='Address'
GROUP BY obj.schema_id, obj.name
ORDER BY Obj.name

GO

Row counts for each tables using sys.partitions

sys.partitions is a SQL Server System Catalog view which contains a row for each partition of all the tables and most types of indexes in the database. Using this you can get a row counts for each tables in database.

USE AdventureWorks2017;
GO

SELECT 
QUOTENAME(SCHEMA_NAME(Tbl.schema_id)) + '.' + QUOTENAME(Tbl.name) AS [TableName],
SUM( P.rows) AS [RowCount]
FROM sys.tables AS Tbl
  JOIN sys.partitions AS P
  ON Tbl.object_id = p.object_id
  AND P.index_id IN ( 0, 1 )

GROUP BY Tbl.schema_id, Tbl.name
ORDER BY Tbl.name


GO

You can see, it returns row counts for each table in database Adventureworks2017.




Also Read..

How to Encrypt a Stored Procedure in SQL Server?

How to Read SQL Server error logs?

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 13,936 total views,  4 views today