Update : Added one more way; Hat tip to Glenn Berry

Running COUNT(*) on a table results in Table scan or Index scan; Both are not preferred. Meta-data views could help avoid that.

• Option 1: Query ‘dm_db_partition_stats’ view

[sourcecode language=”sql”]
SELECT T.name AS [Table_Name]
, I.name AS [Index_Name]
, SPS.row_count AS [Record Count]

FROM sys.tables AS t
INNER JOIN sys.indexes AS i
ON t.object_id = i.object_id
AND I.index_id IN (1,0)
INNER JOIN sys.dm_db_partition_stats AS SPS
ON SPS.object_id = t.object_id
AND SPS.index_id = I.index_id
GO
[/sourcecode]

• Option 2: Query ‘sysindexes’ view. Please note that this is a depricated view.

[sourcecode language=”sql”]
SELECT OBJECT_NAME(id), rowcnt, * FROM sysindexes
WHERE indid IN (1,0)
ORDER BY OBJECT_NAME(id)
[/sourcecode]

• Option 3: Query ‘sys.partitions’ view.

[sourcecode language=”sql”]

SELECT OBJECT_NAME(object_id) AS [ObjectName]
, SUM(rows) AS [RowCount]
, data_compression_desc AS [CompressionType]
FROM sys.partitions WITH (nolock)
WHERE index_id < 2 –ignore the partitions from the non-clustered index if any
AND Object_name(object_id) NOT LIKE N’sys%’
AND Object_name(object_id) NOT LIKE N’queue_%’
AND Object_name(object_id) NOT LIKE N’filestream_tombstone%’
AND Object_name(object_id) NOT LIKE N’fulltext%’
AND Object_name(object_id) NOT LIKE N’ifts_comp_fragment%’
GROUP BY object_id, data_compression_desc
ORDER BY Sum(rows) DESC
OPTION (recompile);
[/sourcecode]

Since we are only querying meta data, the results are instantaneous.

Hope this helps,
_Sqltimes