In this tutorial, we’ll discuss on how to find row count of all the table in one database.
To demonstrate this topic, SQL Server 2008 R2 Express version is being used. The exact version information is
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
You can find your database version information by executing SELECT @@VERSION
We’ll be using AdventureWorks2008R2 here. If you want to get the AdventureWorks database for 2008R2 please browse https://msftdbprodsamples.codeplex.com/releases/view/59211 and download AdventureWorks2008R2-Full Database Backup.zip. You need to restore the database to use this.
For finding the row count of all the tables of a database you can use one Microsoft’s undocumented procedure sp_MSForEachTable.
You can use below query to get all the details
EXEC sp_MSForEachTable @command1='SELECT ''?'', COUNT(*) [Row_Count] FROM ?'
But for better readability we can create a temporary table and inserts all the information into it. Then we can select all the rows from the temporary table to have a better view of the table name and row count.
CREATE TABLE #RowCountsForTables
(
TableName varchar(255),
Row_Count int
)
EXEC sp_MSForEachTable @command1='INSERT #RowCountsForTables (TableName, Row_Count) SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, Row_Count FROM #RowCountsForTables ORDER BY TableName, Row_Count DESC
DROP TABLE #RowCountsForTables
It shows the results
- 84 reads