SQL Server Performance

Get rowcount of all the tables...

Discussion in 'Contribute Your SQL Server Scripts' started by ramkumar.mu, May 3, 2006.

  1. ramkumar.mu New Member

    IF object_id('SP_GetAllTablesRowCount') IS NOT NULL
    PRINT 'Procedure SP_GetAllTablesRowCount dropped'
    DROP PROCEDURE SP_GetAllTablesRowCount

    Procedure Name : SP_GetAllTablesRowCount
    Author Name : Ramkumar Murugesan
    Creation Date : 29 March 2006


    Calculates the rowcount of all the tables in the current database

    1) EXEC SP_GetAllTablesRowCount

    CREATE PROCEDURE SP_GetAllTablesRowCount


    SELECT object_name(id) TableName
    ,max(rowcnt) Rows
    FROM sysindexes
    WHERE object_name(id) IN (SELECT Name
    FROM sysobjects
    WHERE type = 'u')
    GROUP BY object_name(id)


    IF @@error = 0
    PRINT 'Procedure SP_GetAllTablesRowCount created'


    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. Madhivanan Moderator

    Provided that you run
    DBCC UPDATEUSAGE('db_name','table_name') WITH COUNT_ROWS
    before running that script


    Failing to plan is Planning to fail
  3. ramkumar.mu New Member

    wont that DBCC UPDATEUSAGE (0) suffice???


    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  4. Madhivanan Moderator

    Yes. I didnt notice that [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. vbkenya New Member

    Good script but a I have few notes:

    1. There is no need to use MAX() and GROUP BY. The row count for a heap or clustered index is always the maximum number. No non-clustred index would ever have more rows than the underlying heap/clustered index.

    2. You could avoid looking up the type of object ('U') in sysobjects. In SQL Server 2000, all 'user' tables have ids greater than 1977058078. Granted system tables used in replication will have larger ids but you can filter those out if necessary.

    So that leaves you with:

    SELECT object_name(id) TableName,rowcnt Rows
    FROM sysindexes
    WHERE id>1977058078 AND indid IN (0,1)

    Please compare this and the results you get from your procedure.

    Nathan H. Omukwenyi
    MVP [SQL Server]
  6. mmarovic Active Member

    I prefer type = 'u' test over id > whatever number is, because the performance of selecting rows from sysindexes table is not really that important.
    Restricting indid to 0 - heap and 1 - clustered index is good idea that makes the query so much simpler.
  7. vbkenya New Member

    Just trying to provide another way of looking at the same issue. Forgive me for thinking about performance and trying to avoid going through the often larger sysobjects table. I can be obsessive sometimes.

    Nathan H. Omukwenyi
    MVP [SQL Server]
  8. mmarovic Active Member

    Nathan, no need to get upset, if you are, not quite sure. I just thought we were a bit too hard on Ram, so I wanted to mention something he did right IMO.
  9. ramkumar.mu New Member

    Thanks VBK for your suggestions. as MM mentioned, i would prefer type = u than id > though i didnt think of that option.


    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

Share This Page