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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 152 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.