Retrieve Table List with Number of Rows

In this blog we'll see a quick way to list tables with number of rows in a database. The script is particularly useful to analyse which tables are larger and results could potentially be saved to a regular table in order to track growth.
The script is divided into four steps: first, it creates a memory table named rcount (#rcount), which is constituted by two fields. A VARCHAR, to store table names, and an INT, which will expose the rows count.
Using the sp_MSForEachTable stored procedure (a function which is present into 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 first field, while the second one will be the number of records into that particular table, obtained through 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 statement retrieves the records stored into our memory table, sorting them by descending values of the row_count field (i.e., the record with highest number of rows being the first). Since the field which represents the table name will be exposed in the [dbo].[Table_Name] form, we apply through REPLACE function, a cleansing of the field to show the table name without further indicators.