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