Results 1 to 3 of 3

Thread: faster count(*)

  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Unanswered: faster count(*)

    I'm trying to get a rowcount (it doesn't have to be exact) of all the user tables in a database. I'm currently extracting the table names from sysobjects and then just building up some sql to put the results in a TableRowCount table.

    Problem is with very large tables it takes a long time. The sproc sp_spaceused is almost instant so I presume there's a quicker way of doing it. Any ideas ?

    Mike

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If you look at the code for sp_spaceused you should get something like this
    Code:
    select o.name
    ,str(sum(rowcnt(i.doampg)),11,0) rowtotal
    from sysobjects o, sysindexes i
    where o.id=i.id
      and o.type='U'
    group by o.name
    order by o.name

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You're a star!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •