WilberBeast

Using Count(*) to obtain a row count can be very slow on large tables. This is the only truly reliable way to obtain an accurate row count but, post SQL Server 2000, the mess that was sys.indexes, etc. was largely cleaned up and nowadays the row count can be obtained using the following code. Microsoft still states that the row count in sys.partitions is an “Approximate number of rows in this partition”, but it appears to be stable and accurate with appropriate caveats with regards to uncommitted transactions.

Select      s.Name
           ,t.Name
           ,Sum(p.rows) As Rows
From        sys.tables t
Inner Join  sys.schemas s On (s.schema_id = t.schema_id)
Inner Join  sys.indexes i On (i.object_id = t.object_id and i.index_id < 2)
Inner Join  sys.partitions p On (p.object_id = t.object_id)
Where       p.index_id In (0, 1)
Group By    s.Name
           ,t.Name
Order By    s.name
           ,t.name