/****** 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!
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.
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
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.