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