Very Quick Accurate Row Counts

Validated against: SQL Server 2005, 2008, 2008 R2.

;WITH cte([TableName],[IndexName],[RowCount])AS
(SELECT o.[name],i.[name],SUM([p].[row_count])
FROM sys.objects AS o WITH(NOLOCK)
JOIN sys.indexes AS i WITH(NOLOCK)
ON o.[object_id]=i.[object_id]
JOIN sys.dm_db_partition_stats  AS p WITH(NOLOCK)
ON i.[object_id]=p.[object_id] AND i.[index_id]=p.[index_id]
WHERE o.[type]='U'/*Table (user-defined)*/
GROUP BY o.[name],i.[name]
)
SELECT c.[TableName],MAX(c.[RowCount])AS[RowCount]
FROM cte c
GROUP BY c.[TableName]
ORDER BY c.[TableName];