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

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 25, 2006 at 12:07 pm

  #656898

  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

  August 25, 2006 at 12:10 pm

  #656900

  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

  August 25, 2006 at 12:32 pm

  #656908

  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

  August 25, 2006 at 12:46 pm

  #656910

  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

  August 25, 2006 at 1:02 pm

  #656914

  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 .