Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47

    Unanswered: Table list and record count

    Hi.

    My boss would like a list of all the tables in a specific database and their record counts. There are over 80 tables in this database, so doing one by one is not something I have time for.
    Does anyone know of a system table that stores this information or an easy way of doing this?

    Please help.

    Thanks,
    ODaniels

  2. #2
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    Never mind. I found it.

    Thanks anyway.

  3. #3
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    If anyone is interested it's:

    select o.name, max(i.rowcnt) rowcnt
    from sysindexes i inner join sysobjects o
    on i.id = o.id
    group by i.id, o.name
    order by o.name

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I generally prefer:
    Code:
    SELECT i.rowcnt, o.name
       FROM dbo.sysindexes AS i
       JOIN dbo.sysobjects AS o
          ON (o.id = i.id)
       WHERE  i.indid IN (0, 1)
    Note that this is only accurate if your table statistics are current. When in doubt, use:
    Code:
    EXECUTE sp_updatestats 'RESAMPLE'
    -PatP

  5. #5
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    I had originally had the indid in (0,1). I wasn't getting all the tables. Some were being dropped. I found that the query above gave me one of each table. None being dropped.
    Thanks.

Posting Permissions

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