Count Total Rows For All Tables In MS SQL Server

This SQL script below will count and display list of table name along with total rows for all tables in a specified MS SQL Server Database.

Instead of using:

SELECT COUNT(*) FROM TABLE_NAME

… for each table in the database, we can query the system tables as below:

SELECT sysobjects.Name AS TableName, sysindexes.ROWS AS TotalRows
FROM 	sysobjects 
		INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE TYPE = 'U' AND sysindexes.IndId < 2
ORDER BY sysindexes.ROWS DESC

An example from Umbraco database

Example Total Rows For All Tables In MS SQL Server Database