How To Get Table Row Counts Quickly And Painlessly

  • SanjayAttray

    SSChampion

    Points: 13157

    .

    SQL DBA.

  • jenolan

    Newbie

    Points: 5

    🙂 Hi,

    How about just right clicking a DB table and select properties, then under Select a page, choose Storage and on the right side of the screen you will see Row count. This also shows data space and index space.

    FYI.

  • gregg_dn

    Ten Centuries

    Points: 1333

    Enjoyed the article and the comments.

  • Pat Reddy

    SSC Enthusiast

    Points: 107

    To the last 2 posters:

    1) sysindexes will not be supported in future releases.

    2) This was suggested as a quick and easy way to report the # of rows in every table in a database. Right-clickig every table isn't practical.

    I think we've beat this horse to death, haven't we?

    Main points made: It's a nice artical if you want to learn more about DMVs or are interested in the number of rows in every table in your database, but there are other options for getting at the # of rows in a particular table that are considrably simpler.

    🙂

  • Rahul The Dba

    SSChasing Mays

    Points: 647

    nice article but i must say nothing innovative about it ,,,,,,,,

    many sql server user's already knows it,,,,,, as i said many not all so nice n easy article 😛

    [font="Comic Sans MS"]Rahul:-P[/font]

  • nigel.meakins

    SSC Veteran

    Points: 220

    http://www.sqlservercentral.com/Forums/Topic895882-146-1.aspx

    This article states that sysindexes gives an approximate value, which is as documented with BOL. The ssms reports also use this, which can get very different results to the actuals. simply verifying that your approach gives the same result as select count(*) for a single db is hardly good enough testing to suggest we all use this approach. You might want to check your own forums before publishing and misleading, and also msdn docs.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    for myself i use the index scanning DMV and just look at the leaf level for the row count

  • geert.bens

    Grasshopper

    Points: 18

    I'm not sure if this is interesting for programmers, for DBA's sure but programmers are likely to have limited permissions. It is likely they do not have rights to interrogate systemtable or DMV's.

    just a remark 🙂

  • rsingh.sql

    SSC Eights!

    Points: 874

    Interesting article Kendal, I have been using the same dmv as you have mentioned here but with a slight modification and here it is

    Select Object_Name(ddps.object_id) TableName

    , ddps.row_count #Rows

    From sys.dm_db_partition_stats ddps

    Where ObjectProperty(ddps.object_id, 'IsUserTable') = 1

    And ddps.index_id < 2

    Order By Object_Name(ddps.object_id)

  • dmigo

    SSC Journeyman

    Points: 78

    This is an old, long thread and maybe it has already been mentioned but you can customize and add the RowCount column in the Object Explorer Details view for 'tables' in SQL 2008 SSMS.

    So how does SSMS get it?

    Like this:

    (edited out of a Profiler trace)

    select tbl.name AS [Name],

    SCHEMA_NAME(tbl.schema_id) AS [Schema],

    ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]

    FROM

    sys.tables AS tbl

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    dmigo (2/1/2011)


    This is an old, long thread and maybe it has already been mentioned but you can customize and add the RowCount column in the Object Explorer Details view for 'tables' in SQL 2008 SSMS.

    So how does SSMS get it?

    Like this:

    (edited out of a Profiler trace)

    select tbl.name AS [Name],

    SCHEMA_NAME(tbl.schema_id) AS [Schema],

    ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]

    FROM

    sys.tables AS tbl

    how long does this take to run compared to select count(*)?

    we have some daily reports that give us the row counts in publishers and subscribers that take a long time to run sometimes on tables with tens of millions of rows

  • homebrew01

    SSC Guru

    Points: 55199

    No one has mentioned this method:

    select * from tableA

    then scroll down to the bottom to see how many rows there are

    :Whistling::w00t::hehe::-D

  • SQLRNNR

    SSC Guru

    Points: 281252

    homebrew01 (2/2/2011)


    No one has mentioned this method:

    select * from tableA

    then scroll down to the bottom to see how many rows there are

    :Whistling::w00t::hehe::-D

    Evil :-D:w00t::hehe:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    CirquedeSQLeil (2/8/2011)


    homebrew01 (2/2/2011)


    No one has mentioned this method:

    select * from tableA

    then scroll down to the bottom to see how many rows there are

    :Whistling::w00t::hehe::-D

    Evil :-D:w00t::hehe:

    I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:

  • SQLRNNR

    SSC Guru

    Points: 281252

    Ninja's_RGR'us (2/8/2011)


    CirquedeSQLeil (2/8/2011)


    homebrew01 (2/2/2011)


    No one has mentioned this method:

    select * from tableA

    then scroll down to the bottom to see how many rows there are

    :Whistling::w00t::hehe::-D

    Evil :-D:w00t::hehe:

    I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:

    Where are the cursor or recursive cte methods for this?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 91 through 105 (of 109 total)

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