Find Table Size

[NickName]

Env: SQL Server 2000

The following sql stmt seems to find a particular table's size
programmatically:

select top 1 [rows],rowcnt
from sysindexes
where ID = object_id('aUserTable')
and status = 0
and used > 0

However,
a) I'm not 100% sure of its consistency;
b) Both [rows] col and [rowcnt] col seems to produce same data, which
one is supposed to be more accurate (or more up to date)?

TIA.

[Erland Sommarskog]

NickName (da****@rock.com) writes:

The following sql stmt seems to find a particular table's size
programmatically:

select top 1 [rows],rowcnt
from sysindexes
where ID = object_id('aUserTable')
and status = 0
and used > 0

However,
a) I'm not 100% sure of its consistency;
b) Both [rows] col and [rowcnt] col seems to produce same data, which
one is supposed to be more accurate (or more up to date)?

A TOP 1 without ORDER BY is not a good thing. However, if you with
"size" means rowcount, this may do. Better though is to add the
"AND indid IN (0, 1)" to the WHERE clause. (There is never rows for
both 0 and 1, but always for exactly one of them.)

The values in sysindexes are not the exact values, for that you need to
do SELECT COUNT(*). However, a DBCC UPDATEUSAGE before you run the
SELECT query will give you good accuracy.

Judging from the documentation, rowcnt is the better column to use.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[NickName]

Erland,

Yes, when I say table size I meant row count of a table.

Results of some testing of the following queries:

select rowcnt
from sysindexes
where ID = object_id('customer')
-- and status = 0
and used > 0
AND indid IN (0, 1)
produces correct resultset

select rowcnt
from sysindexes
where ID = object_id('customer')
and status = 0
and used > 0
AND indid IN (0, 1)

produces incorrect resultset (zero count)
Not to use SELECT COUNT(*) ... is because I intend to get row count for
each and all tables of a huge database, COUNT would take considerable
longer to do.

You're the man!

Don