Fortunately you can obtain the number of rows without having to run such a heave I/O intensive operation.
In Sql Server 2000 the command looks like this:
Select OBJECT_NAME(ID), rows From sysindexes Where id = OBJECT_ID(
While I found that the number of rows will differ slightly it is close enough to make needed judgment calls based on row count. (I.E. defragging, partitioning, archiving, etc.)
In Sql Server 2005 the command looks like this: SELECT OBJECT_NAME(object_id) TableName, rows FROM sys.partitions WHERE OBJECT_NAME(object_id) =
note, be sure to replace TableName with your name in order to locate the rowcount for the individual table. You can alternatively modify the Where clause to be by Rows > x so you can find tables over a specified number of rows. Also, if there are more than one index you may end up yielding several responses as the entries in both the partition table and the sysindexes table count the rows in ... you guessed it the index.