I agree with previous posters that this seems like a good way to find row counts for all tables in a database, but not a very good (easy, efficient, etc...) alternative to COUNT(*) on a single table within a query. I created the following proc and tested with my largest tables - hundreds of millions of rows with a non-sequential GUID pk and the results were generally 1 sec for COUNT(*) and 0 sec for the proc. Considering how I use COUNT(*) in my logic (and it is rarely used), it doesn't seem worth it - merely a nifty way to complicate things.

CREATE PROC spUtil_GetRowCount

(

@TableName VARCHAR(200)

)

AS

SELECT

ddps.row_count

FROM

sys.indexes i

INNER JOIN sys.objects o

ON i.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats ddps

ON i.OBJECT_ID = ddps.OBJECT_ID

AND i.index_id = ddps.index_id

WHERE

i.index_id < 2

AND o.is_ms_shipped = 0

AND o.name = @TableName