If you can deal with a rough count on SQL 2000 - you can query sysindexes - something like
Under SQL 2005+ the equivalent would be
The row count returned from 2005+ will be accurate, unless your table has no clustered index (i.e. is a heap).
These methods are orders of magnitude faster than counting all the rows using COUNT().
answered Oct 21 '09 at 04:01 PM
Matt Whitfield ♦♦
You could also use:
I specified index_id = 1, since you mentioned that all table have a PK, which I inferred to be the clustered index. You could also specify index_id < 2, which would account for a HEAP table
one of the returned columns is "rows"
Alternatively, of course, you can run:
If you need to store the count in a variable, then use this:
answered Oct 21 '09 at 03:52 PM
I use the dm_db_index_physical_stats as well. it runs nightly as part of maintenance
answered Oct 21 '09 at 04:19 PM