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.
CREATE TABLE [RowCount](
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
|