Technology Toolbox

Your technology Sherpa for the Microsoft platform

Jeremy Jameson - Founder and Principal

Search

Search

Counting Rows in All Database Tables in SQL Server

Note
This post originally appeared on my MSDN blog:

Since I no longer work for Microsoft, I have copied it here in case that blog ever goes away.

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

  1. # re: Counting Rows in All Database Tables in SQL Server

    July 7, 2008 9:49 AM
    sidsan2k

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

    thanks once again

    sanjeev

  2. # re: Counting Rows in All Database Tables in SQL Server

    April 26, 2010 10:16 AM
    Willy

    Hi,

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

    Table '<name_of_myBD.sysobjects>' doesn't exist

  3. # re: Counting Rows in All Database Tables in SQL Server

    April 26, 2010 4:14 PM
    Jeremy Jameson
    Gravatar

    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. # re: Counting Rows in All Database Tables in SQL Server

    September 2, 2010 1:49 AM
    Stephan

    Replace

    ORDER BY

       sysobjects.Namecode

    with

    ORDER BY

       sysobjects.Name

    for SQL 2008

  5. # re: Counting Rows in All Database Tables in SQL Server

    September 15, 2010 6:16 AM
    oli

    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. # re: Counting Rows in All Database Tables in SQL Server

    November 30, 2010 1:36 AM
    Experts Comment

    Crisp and clean solution.

    www.a2zmenu.com/.../Row-count-of-all-tables-in-a-database%20.aspx

  7. # re: Counting Rows in All Database Tables in SQL Server

    March 4, 2011 9:38 AM
    Russ Smith

    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

  8. # re: Counting Rows in All Database Tables in SQL Server

    July 19, 2011 12:22 AM
    Avnish Kumar

    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.

  9. # re: Counting Rows in All Database Tables in SQL Server

    July 20, 2011 11:46 AM
    Ali Delshad

    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

  10. # Weekly Link Post 45 « Rhonda Tipton’s WebLog

    June 8, 2008 5:20 PM
    rtipton.wordpress.com

Add Comment

Optional, but recommended (especially if you have a Gravatar). Note that your email address will not appear with your comment.
If URL is specified, it will be included as a link with your name.

To prevent spam from being submitted, please select the following fruit: Strawberry

Cherries
Strawberry
Grapes
Apple
Pear
Watermelon
 
Please add 6 and 4 and type the answer here: