Count the Number of Records for each Database Tables in MS SQL

Paopatsin/ October 24, 2020/ Database, MS SQL, Technology

Please read our Disclaimer

Works With
-Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
-Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)

In case you need a quick run of how many records (or rows) are there in your database tables.

Command:

--Create a temporary storage to store our result
CREATE TABLE #tmpList
(
   tableName VARCHAR(255),
   recordCount INT
)
--We will loop thru using an MS SQL built-in stored procedure
EXEC sp_MSForEachTable @command1 = 'INSERT #tmpList (tableName, recordCount) SELECT ''?'', COUNT(*) FROM ?'
--View the result
SELECT tableName, recordCount FROM #tmpList ORDER BY tableName ASC
--Release the temporary storage
DROP TABLE #tmpList

Steps and Sample Usage:

1. Open and log into your SQL Management Studio
2. Open a New Query Windows or (CTRL+N)
3. Copy and Paste the code block above to your Query Window
4. Execute the query by pressing F5.

Use Case:

1. Use when you need a quick overview of how many records exists in your database tables.

Sample Data:
-AdventureWorks2016 Database