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

--> (Word)	--> (PDF)	--> (Epub)	--> (Text)
--> (XML)	--> (OpenOffice)		--> (XPS)
This article has been published [fromdate]
[readtime]

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.

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.

SOURCE	LINK (Kodyaz.com)	LANGUAGE	ENGLISH