Adventures in SQL: SP_MSFOREACHTABLE to find the largest table

Posted on by Jen Stirrup
When exploring a SQL Server database for the first time, it’s always useful to identify the most populated tables first because they are very likely to be the most important tables. To do this in a situation where there are tight time-scales, it’s possible to use some undocumented SQL Server procedures to quickly produce a list of the tables which have the most rows.
Before proceeding, however, it’s wise to note that undocumented stored procedures are not guaranteed by Microsoft. Further, they are not ‘fixed’ so they could change without warning, so use this command at your own discretion, and treat the results with care!
One example of an undocumented SQL Server procedure which has been useful is SP_FOREACHTABLE.  Although it’s not guaranteed, it might be useful for some to try it out as a starting point when exploring data, when it’s useful to find out the most populated tables in your new unexplored database.
In this example, a new table called TableRowCount will be created. This table will hold the results of the execution of SP_FOREACHTABLE.  To assist you, the SQL to do this is given below:

/****** Check if table TableRowCount exists already, and if so, delete it    ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableRowCount]’) AND type in (N’U’))
DROP TABLE [dbo].[TableRowCount]
GO
/****** Create table TableRowCount                                                         ******/
CREATE TABLE [TableRowCount](
  TableName sysname,
  [TableRowCount] int )

Once the table is created, the SP_FOREACHTABLE procedure is executed as follows:

/****** Execute stored procedure, and save results in table TableRowCount ******/
EXEC sp_MSForEachTable ‘INSERT [TableRowCount](TableName, [TableRowCount]) SELECT ”?”, COUNT(*) FROM ?’

Then, it’s possible to retrieve the results of this command from the TableRowCount table, as follows:

/****** Display the results from table TableRowCount, in descending order ******/

select * from TableRowCount
order by TableRowCount.TableRowCount desc

The above statement will sort the data so that the table with the highest number of rows will appear at the top. It’s a guess that the most populated table is an important one, but it’s not a bad guess all the same!

3 thoughts on “Adventures in SQL: SP_MSFOREACHTABLE to find the largest table”

  1. Hi Jen,
    On large systems, the method you outlined here will cause table/index scans and you can use sys.allocation_units to retrieve that data quickly. The data in sys.allocation_units will be slightly off when you do bulk inserts/index rebuilds though.

    Reply

  2. Hello Sankar,
    Thank you for your generous reading of my blog! I write my blog so that I learn from other people, and your comments are very helpful.
    Kind Regards,
    Jen

    Reply

  3. I often use sp_MSForEach* stored procedures myself. Especially sp_MSforeachdb.

    In your specific situation. I'll add a third option (to yours and Sankar's).

    I often use:

    SELECT t.name as [tableName],
    ps.row_count
    FROM sys.tables t
    JOIN sys.dm_db_partition_stats ps
    ON t.object_id = ps.object_id
    WHERE ps.index_id IN (1,0)
    ORDER BY ps.row_count DESC

    The index_id = 1 filters for the clustered tables and the index_id = 0 filters for the heaps (there are no other kinds).
    There used to be a problem with accuracy (as Sankar mentioned) But I've never been able to reproduce the discrepancy.

    But I don't want to downplay the power of sp_MSForEach*. THey can be *extremely* powerful.

    Reply

Leave a Reply