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]
/****** Create table TableRowCount ******/
CREATE TABLE [TableRowCount](
[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!