SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database

If you are looking for a simple t-sql solution to count all rows in all tables in a MS SQL Server database, then we can develop and code a sample solution by using the undocumented sp_MSForEachTable stored procedure.

Below t-sql sp_MSForEachTable example showing a basic use of this sp_MSForEachTable stored procedure.
T-SQL developers will find the source codes of sql function udf_TableRowsCount at sql tutorial for SQL rowcount of a table using sys.partitions system view.

EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
-- OR
EXEC sp_MSForEachTable 'SELECT ''?'', dbo.udf_TableRowsCount(''?'')'

But the output is not well formated for our purposes. Since each select returns the result set of own. But our requirement for summing the count of each select statement is fetching all results in a single result set.

Instead of aggregating result set in a single select, I can code an Insert statement in the sp_MSForEachTable command text parameter.
If I insert the count of records in a different table, later I can run a sql aggregate select command to sum the results.

Let's start by building the temporary table for storing the results of the sp_MSForEachTable example statement.

  TableName sysname,
  [RowCount] int

Now the sample sp_MSForEachTable command requires some modification.

EXEC sp_MSForEachTable 'INSERT [RowCount](TableName, [RowCount]) SELECT ''?'', COUNT(*) FROM ?'

-- OR

EXEC sp_MSForEachTable 'INSERT [RowCount](TableName, [RowCount]) SELECT ''?'', dbo.udf_TableRowsCount(''?'')'

As you can see, I altered the sp_MSForEachTable command to an INSERT statement instead of a SELECT statement.
This will insert the row count for all tables in a SQL database into the RowCount table.

As a last step, we can execute the below T-SQL SELECT command in order to get the final result.

SELECT SUM([RowCount]) as TotalNumberOfRowsInDatabase FROM [RowCount]

That is all. I hope you enjoyed this sample sql code script as a sp_MSForEachTable example for getting the count of all rows in all tables in a MS SQL Server database.

More Tutorials on T-SQL sp_MSForEachTable Examples

sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications
Create Database Snapshot for all Databases on SQL Server

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.