Row count for all my tables

Question

Post reply

Row count for all my tables

Isabelle2378

SSCertifiable

Points: 6174
More actions
August 25, 2006 at 10:54 am

#114221

Hi,
I found this script to display all tables and row count for each, but it only returns tables owned by dbo. How can I modify it to show other schemas as well in the database?
SELECT

[TableName] = so.name,
[Table id]=so.id,
[RowCount]
= MAX(si.rows)
FROM
sysobjects so
, sysindexes si
WHERE

so
.xtype = 'U'
and si.id = OBJECT_ID(so.name)
GROUP
BY
so
.name,so.id
ORDER
BY
2
DESC

Thanks!!
Isabelle
Thanks!
Bea Isabelle

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

hmm, maybe this...

SELECT

[TableName] = so.name,

[Table id]=so.id,

[RowCount]

= MAX(si.rows)

FROM

sysobjects so

, sysindexes si

WHERE

so

.xtype = 'U'

and si.id = so.id

GROUP BY

so.name,so.id

ORDER

BY

so.id

DESC

Because I don't see anything that filters the owner...

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

Thank you. That worked. What does the object_id(so.name) do that it causes other schemas not to show up?

Isabelle

Thanks!
Bea Isabelle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Couldn't find anything out about that... but that was the only thing I could see that may have a chance of screwing something up.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 4

I couldn't either. When I did a comparison for the values in sysobjects and sysindexes for my database, I saw that for the table owned by dbo there was a value in OBJECT_ID(so.name), but in all the tables owned by a different schema, they were NULL and that's why they are not getting picked up. I just don't understand what OBJECT_ID is doing or where that value is coming from.

Thanks for your help.

Isabelle

Thanks!
Bea Isabelle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I'm certain it queries sysobjects but I never saw the query so I'd just be speculating at this point... But as long a syour query works now .