Counting Rows in All Database Tables in SQL Server


Here is yet another of the SQL scripts that I like to keep handy in my toolbox: Count Records in All Tables.sql

Sometimes when I get “dropped into” a consulting situation with a new customer, I need to quickly get acquainted with one or more of their SQL Server databases. One of the first things I usually like to know is: “What are the largest tables in the database in terms of the number of rows?”

While you could certainly craft some SQL to SELECT COUNT(*) from each user table, this is very inefficient. A much better way is to simply query the system tables as shown below:

SELECT
    sysobjects.Name
    , sysindexes.Rows
FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id
WHERE
    type = 'U'
    AND sysindexes.IndId < 2
ORDER BY
    sysobjects.Namecode
Comments (10)

  1. sidsan2k says:

    Thanks for the query .. I was looking for this , got after extreme search from you. good

    thanks once again

    sanjeev

  2. Willy says:

    Hi,

    It doesn’t work for me in mysql. It always gives me:

    Table ‘<name_of_myBD.sysobjects>’ doesn’t exist

  3. Did you mean to have a space in there — i.e. "my sql" as in "my SQL Server"? Or are you referring to MySQL, as in the database for the LAMP stack?

    If you are using Microsoft SQL Server, let me know which version and I’ll see if I can help you out.

    If you are using MySQL, then, sorry, but you’ll have to seek help elsewhere. I have no experience whatsoever with MySQL.

  4. Stephan says:

    Replace

    ORDER BY

       sysobjects.Namecode

    with

    ORDER BY

       sysobjects.Name

    for SQL 2008

  5. oli says:

    For me count 207 rows and when i make "select count(*) from tableName" show me 209 rows why (This happen only in case of 3 tables) Why?

  6. Russ Smith says:

    Nice and simple, thanks this is really helpful.  Any idea on how i could add something to the query to get a sum of all the row counts?  Something returned like:

    TableA    12

    TableB    33

    TableC     5

    Total        50

    Thanks in advance!

    -Russ

  7. Avnish Kumar says:

    I want to select count(1) from random tables in a database.

    for eg. we have a database MYDB with tables a,b,c,d,e……x,y,z.

    I want to select any 5 random tables and take its count.

    Can anyone suggest how to do it.

  8. Ali Delshad says:

    very good and If you want to retrieve only sum of all rows in all databases just use these codes :

    SELECT sum (sysindexes.Rows)

    FROM

       sysobjects

       INNER JOIN sysindexes

       ON sysobjects.id = sysindexes.id

    WHERE

       type = 'U'

       AND sysindexes.IndId < 2