Problem getting row count from sysindexes 1

[BlakeK]

I need to get the row count for all the tables in my databse.
Rather than doing:
select count(*) from table_name

I was going to do:
SELECT rows FROM sysindexes
WHERE name = 'table_name'

But this does not always return a row.
For 90% of the tables in my database it does, but for the other 10% no row exists in sysindexes. I'm not sure why this is.

Someone else suggested doing it like this:
SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2

Now, that always returns a row for every table, but the value it returns does not always match with the results of select count(*)

Any ideas or thoughts on what is wrong with how I am doing this, or the quickest way to retrieve row count?

Thanks.

 

[JamesLean]

One method is to use the undocumented stored proc sp_MSforeachtable:

EXEC sp_msforeachtable 'SELECT ''?'' AS table_name, COUNT(*) AS row_count FROM ?'

--James

 

[BlakeK]

Thanks James, I didn't know about that SP. However, it is basically the same thing as doing the select count(*) in a cursor that I mentioned above.
FYI, the sp_MSforeachtable took 6 minutes and 7 seconds to run on my DB.
The lookup into sysindexes is basically instantaneous.
I guess I'm just more curious as to why there is not a row for every table in sysindexes for the name ='table_name' example I used above, and why the row count in sysindexes is not correct for the id = OBJECT_ID('table_name') example.

Thanks for your input.

 

[vongrunt]

> I guess I'm just more curious as to why there is not a row for every table in sysindexes for the name ='table_name' example I used above

Because sysindexes.name is name of index. Name of table is something to find in table sysobjects.

> and why the row count in sysindexes is not correct for the id = OBJECT_ID('table_name') example.

Dunno... this obviously happens and you are not the first one to report that

 

[BlakeK]

>Because sysindexes.name is name of index. Name of table is something to find in table sysobjects.

I realize there are rows in sysindexes for primary key, indexes, etc. but there is also a line for 90% of my tables where the "name" field is the name of the table, and the "rows" value is the exact number of rows in the table.
For the rows in sysindexes where the "name" field is the table name, the indid is 0
The primary key is indid 1, and the "name" field is the PK name.
Additional indid rows for each tabel object are the indexes which I have created, followed by lines with "name" fields that look like "_WA_Sys_vpn_type_id_1368B15D" which I have no idea what those mean!
The 10% of my tables which do not have a row in sysindexes with a "name" field corresponding to the table, have no row with indid = 0, just indid = 1 that is the PK, followed by the index indid rows, and those _WA_ rows.

 

[vongrunt]

Some clarifications:

Indid=0 - entry for table without clustered index. Name contains table name
Indid=1 - clustered index. Name usually begins with "PK_" (auto-generated).

For both cases join on sysobjects (via column id) will provide information about object (table). Wanna select only user tables, add sysobjects.xtype='U' in WHERE clause.

Primary key may not be always implemented as clustered index. This is just default SQL Server behavior, suitable for OLTP applications.

True detection of primary key is undocumented IMO - you can pull it out with:
CASE WHEN (sysindexes.status & 0x800) = 0x800 THEN 1 ELSE 0 END

_WA_* stuff is for column statistics.

 

[BlakeK]

Great clarifications on the sysindexes info, thanks vongrunt!