In this article I am going to show how we can count
total number of rows in every SQL Server table.
I have 2 below Tables in
my Data Base:

Image 1.
Now Select Count From
these tables:

Image 2.
Now by using below SQL
Statement you can find All Tables with their rows count:

Image 3.
SELECT TableName = o.name,
Rows = max(i.rows)
FROM sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id
WHERE xtype = 'u'
AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY o.name
ORDER BY Rows
DESC
GO
We can use below SQL
Statement also to get this type of result:

Image 4.
DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString
+ ' UNION ALL ','')
+ 'SELECT '
+ '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
, COUNT(*) AS [RowCount]
FROM '
+ QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped
= 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO