Post reply

How to get Table row count?

  • June 2, 2006 at 9:00 am

    #172878

    How can I get a table row count from sqlserver besides the following method?

    select count(*) from tablename

    In Oracle after I gather stats for a table I can use the following query.

    select TABLE_NAME, num_rows from user_tables where table_name='X';

    Is there a dictionary view that I can query from to get the row count for a table?

    We are going to do an upgrade and I wanted to get an easy method to capture the row count before and after the upgrade to ensure they are the same.

  • June 2, 2006 at 10:06 am

    #641493

    SELECT count(*) is the best way to get current row count in SQL 2000.  SQL 2005 has dynamic dictionary views like Oracle.

    Greg

    Greg

  • June 2, 2006 at 11:40 am

    #641508

    Thanks, this I understand I was hoping for a magic SQL summary statement for the database rather than running 684 selects

  • June 2, 2006 at 10:59 pm

    #641550

    Hi all,

     

    How about select count(*) for the row(s) that created on that date, after that date or before that date?

     

    thanks in advance!

  • June 5, 2006 at 1:44 am

    #641603

    Hi,

    For your previous question, you can use sysindexes  table also, inorder to count each rows in the table, directly we can read the no of records deails from this table.

    Eg.

    SELECT rowcnt from sysindexes WHERE name='sysusers'

    Please correct me if iam wrong.

    warmest

    Jayakumar K.

    Thanks
    Jay
    http://www.sqldbops.com

  • June 5, 2006 at 3:26 am

    #641612

    When using the sysindexes for information use the dbcc updateusage with count_rows before the query.

    The information in sysindexes can be way off. It happened to me. Tables filled with millions of reconds, but in sysindexes only a couple or none for the rowcnt.

    <<< Information from the manual >>>

    DBCC UPDATEUSAGE

    Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

    Syntax

    DBCC UPDATEUSAGE

        (    { 'database_name' | 0 }

            [ , { 'table_name' | 'view_name' }

            [ , { index_id | 'index_name' } ] ]

        )

        [ WITH    [ COUNT_ROWS ] [ , NO_INFOMSGS ]

            ]

  • June 5, 2006 at 7:02 am

    #641649

    Try this:

    http://www.mssqlcity.com/Articles/KnowHow/RowCount.htm

    Thanks...Chris

  • June 5, 2006 at 10:27 am

    #641781

    Ballack,

    You're talking about something that you can only get if you audit DML statements on a table or if you use a 3rd party transaction log reader, like Log Explorer.  There's nothing built in to SQL Server that tracks update history.

    Greg

    Greg

  • June 5, 2006 at 10:31 am

    #641784

    This should help you.

    SELECT rows FROM sysindexes WHERE id = OBJECT_ID('<TABLE_NAME>') AND indid < 2

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • June 8, 2006 at 2:20 am

    #642447

    And if you don't want to play with system tables then just run the sp_spaceused YourTable system stored proc to get an approximative count. if you run dbcc updateusage before and you dont have and insert and delete in the mean time then the result will be accurate.

    But sp_spaceused gives you a good estimate.

    Anyhow if you need and exact row count there is only select count(*) as the only way.



    Bye
    Gabor

Viewing 10 posts - 1 through 9 (of 9 total)

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