John,

That's a great stored procedure to use for iterating through all the tables. In fact that Stored Procedure was introduced back in SQL Server 6.5; it’s always been an undocumented stored procedure.

I have chosen not to use this procedure because you can’t put the results in an ORDER, SUM the results, or omit system tables (i.e. sysdiagrams) without having to use a temp table and performing a SELECT on that temp table, to not have those features would not be helpful for a database that contains millions of records, 100’s or 1000’s of tables; as you can’t easily determine the results for any type of reports or other practical uses because of the default order is not logical (for reporting/reading purposes).

To make this into a useable method you would need to create a temporary table and INSERT INTO that temp table and then perform a SELECT to pull the information you want and present it in the ORDER you find most useful, as well as SUM the results, and omit any tables you would like.

The problem I have with it is that it increases your overhead by more than 500% (this is based on a small table with 160 tables and 3,000,000 records); I couldn’t imagine what the overhead would be on a medium sized table, or yet a large table. It’s a scary thought in a world where performance is everything!

A great way to see this is to perform my script and the below method on AdventureWorks database; you may be surprised to find the execution time is way different…and this is only with 70 tables and a little over 500,000 records! Imagine a terabyte sized database?!

An example to get the same information as my script, and allow the ability to ORDER, SUM, and omit would be:

USE DATABASE_NAME;

GO

CREATE TABLE #t_rCount (vTableName varchar(50), rcount int);

EXEC SP_MSForEachTable

'INSERT INTO #t_rCount SELECT "?", count(*) from ?';

SELECT * FROM #t_rCount

WHERE vTableName <> '[dbo].[sysdiagrams]'

ORDER BY vTableName

COMPUTE SUM(rcount);

DROP TABLE #t_rCount;

I would discourage using this Stored Procedure alone if you need any type of ORDER or Aggregation function performed, or if you need to place this in a report or a logically readable format (i.e. by table name or row count). I would also highly discourage the use of this stored procedure with a temp table as the base performance overhead is well beyond any acceptable levels.

I hope this makes it clear as to why I had chosen not to use the ‘SP_MSForEachTable’ stored procedure.

Thanks,

James Rea