Post reply

script to see total number of records in all the tables in one db

  • March 10, 2009 at 8:20 am

    #126866

    Hello,

    Can someone help me with the script which gives " Total number of records for all the tables in one particular database togather?

    Thanks in advance,

  • March 10, 2009 at 8:26 am

    #956817

    if you search for "count all rows", there are like 10 different versions of this script in the contributions.

    there's two ways to do it:

    use the indexes to get the counts, which is VERY fast, but can be off by a few rows, or the SLOW way, to really do a COUNT(*) on each table.

    fast way for ALL databases:

    sp_msforeachdb @command1 = 'Select

    ''?'' as db,

    S.name as schemaname,

    T.name as tablename,

    P.rows

    from

    sys.partitions P Join

    sys.tables T On

    P.object_Id = T.object_id Join

    sys.schemas S On

    T.schema_id = S.schema_id

    Where

    P.index_id in (0,1)'

    --The Where clause takes the row count only from the HEAP (0) or Clustered Index (1).

    here's both the FAST and the SLOW way :

    CREATE PROCEDURE sp__CountTableRows

    AS

    BEGIN

    SELECT sysobjects.name, MAX(sysindexes.rows) AS NUMROWS

    FROM sysobjects

    INNER JOIN sysindexes ON sysobjects.id = sysindexes.id

    WHERE sysobjects.xtype = 'U'

    GROUP by sysobjects.name

    ORDER BY NUMROWS DESC,sysobjects.name

    END

    GO

    create table #rowcount (tablename varchar(128), rowcnt int)

    exec sp_MSforeachtable

    'insert into #rowcount select ''?'', count(*) from ?'

    select * from #rowcount

    order by tablename

    drop table #rowcount

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 10, 2009 at 8:34 am

    #956829

    Thanks for your quick reply...

  • March 10, 2009 at 8:50 am

    #956853

    or: use the Disk Usage by Table report. Besides the rowcounts it will also give you the space usage stats for each table....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 3 (of 3 total)

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