Retrieve Table List With Number of Rows


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.


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.


Up Next
    Ebook Download
    View all
    View all