Getting list of all database tables with row counts in SQL Server

Sometimes when you are working with big size databases in SQL Server, you were interested in listing all the database tables along with the row count against each table. Here is an example on how it will look like if you can do this:

The above picture lists all the tables from NORTHWND database and also shows number of rows from each table.

Ok, now we will look into on how we can achieve this in SQL Server. There are couple of methods to do the same, but here I would like to provide the two best methods. For demonstration purpose here I was using SQL Server 2014.

Method 1: The first method is the very simple and easiest way by running the predefined report within SQL Server.

Go to SQL Server, Right click on database -> Reports -> Standard Reports -> Disk Usage by Top Tables

Method 2: The second one is the query base process where we will use a T-SQL statements to get the list of all tables with row counts. Here is the query I have built using CURSORS:

USE NORTHWND
GO

CREATE TABLE #TablesWithRowCount
(
TableName NVARCHAR(150),
TotalRowCount INT
)

DECLARE @tblName NVARCHAR(150)

DECLARE DBtables CURSOR FOR 
SELECT name from SYS.TABLES 

OPEN DBtables
FETCH NEXT FROM DBtables INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN    
DECLARE @SQL NVARCHAR(MAX)
DECLARE @count INT
DECLARE @totalRows INT

SET @totalRows = 0

SET @SQL= 'SELECT @count = COUNT(*) FROM [' +  @tblName + ']'
EXECUTE sp_executesql @SQL, N'@COUNT INT OUT', @totalRows OUT
INSERT INTO #TablesWithRowCount VALUES (@tblName, @totalRows)    
        
FETCH NEXT FROM DBtables INTO @tblName
END 

SELECT * FROM #TablesWithRowCount ORDER BY TableName
DROP TABLE #TablesWithRowCount

CLOSE DBtables
DEALLOCATE DBtables

GO

Hope this helps. Have any questions? Let’s throw them in below comment box...