Get record counts for all tables in MySQL database

mysql sql rowcount
Mark picture Mark · Nov 13, 2008 · Viewed 330.4k times · Source

Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count() on each table?

Answer

Hates_ picture Hates_ · Nov 13, 2008 
SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).

Related questions

Return number of rows affected by SQL UPDATE statement in Java

I'm using a MySQL database and accessing it through Java. PreparedStatement prep1 = this.connection.prepareStatement( "UPDATE user_table SET Level = 'Super' WHERE Username = ?"); prep1.setString(1, username); The update statement above works fine however I'd like to get the number of …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More