Retrieve Table List With Number of Rows

Scope

This article shows a quick way to list tables with a number of rows in a database. The script is particularly useful to analyze which tables are larger and the results could potentially be saved to a regular table to track growth against time.

Script

The script is divided into four steps. First, it creates a memory table, named rcount (#rcount), that consists of two fields. A VARCHAR, to store table names and an INT that will expose the rows count.

Using the sp_MSForEachTable Stored Procedure (a function that is present in the master database, in Programmability > Stored Procedure > System Stored Procedure section), the instruction referenced by the variable @command1 will be executed. It will insert a record having the table name as the first field, whereas the second one will be the number of records into that specific table, obtained through the COUNT(*) function.

  1. CREATE TABLE #rcount(table_name varchar(255), row_count int)  
  2. EXEC sp_MSForEachTable @command1='INSERT #rcount (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'  
  3. SELECT REPLACE(REPLACE(table_name, '[dbo].['''), ']'''), row_count FROM #rcount ORDER BY row_count DESC  
  4. DROP TABLE #rcount  

At the end of this process, the next SELECT retrieves the records stored into our memory table, sorting them by descending values of the row_count field (in other words, the record with the highest number of rows being the first). Since the field that represents the table name will be exposed in the [dbo].[Table_Name] form, we apply, usihng the REPLACE function, a cleansing of the field, to show the bare table name, without further indicators.

Finally, once the data is displayed, the temporary table is deleted.

References