How many rows are there?
This question comes up quite a bit when DBAs are faced with space issues... the simple solution most people say is to do a quick SELECT COUNT(*) FROM , but that ends up being such a costly solution and heavy I/O cost if you have a lot of tables or a lot of data.
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(TableName) AND indid < 2
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) =TableName
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.
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.
Comments
Post a Comment