次の方法で共有

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

  • Anonymous
    June 08, 2008
    PingBack from http://rtipton.wordpress.com/2008/06/08/weekly-link-post-45/

  • Anonymous
    July 07, 2008
    Thanks for the query .. I was looking for this , got after extreme search from you. good thanks once again sanjeev

  • Anonymous
    April 26, 2010
    Hi, It doesn't work for me in mysql. It always gives me: Table '<name_of_myBD.sysobjects>' doesn't exist

  • Anonymous
    April 26, 2010
    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.

  • Anonymous
    September 01, 2010
    Replace ORDER BY    sysobjects.Namecode with ORDER BY    sysobjects.Name for SQL 2008

  • Anonymous
    September 14, 2010
    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?

  • Anonymous
    November 29, 2010
    Crisp and clean solution. www.a2zmenu.com/.../Row-count-of-all-tables-in-a-database%20.aspx

  • Anonymous
    March 04, 2011
    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

  • Anonymous
    July 18, 2011
    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.

  • Anonymous
    July 20, 2011
    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