Count All Tables rows in SQL Server

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