471,054 Members | 1,649 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,054 software developers and data experts.

Find Table Size

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.

Jul 23 '05 #1
2 6607
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
Jul 23 '05 #2
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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Bob Stearns | last post: by
1 post views Thread by Robin9876 | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.