Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Scope
This article we'll see a quick way to list of tables with number of rows in a database. The script is particularly useful to analyze which tables are larger and results could potentially be saved to a regular table in order to track growth against time.
Script
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.
CREATE TABLE #rcount(table_name varchar(255), row_count int)
EXEC sp_MSForEachTable @command1='INSERT #rcount (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT REPLACE(REPLACE(table_name, '[dbo].[', ''), ']', ''), row_count FROM #rcount ORDER BY row_count DESC
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 (i.e., the record with the 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 bare table name, without further indicators.
Finally, once data is displayed, the temporary table is deleted.