Get record counts for all tables in MySQL database
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).
MySQL - How to count all rows per table in one query
SELECT
TABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'YOUR_DB_NAME';
MySQL: Summarize all table row-counts in a single query
The first example code here is a stored procedure which performs the entire process in one step, so far as the user is concerned.
BEGIN
# zgwp_tables_rowcounts
# TableName RowCount
# Outputs a result set listing all tables and their row counts
# for the current database
SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SET @dbname = DATABASE();
SELECT
GROUP_CONCAT(
CONCAT (
'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ',
table_name, ' '
)
SEPARATOR 'UNION '
) AS Qry
FROM
information_schema.`TABLES` AS t
WHERE
t.TABLE_SCHEMA = @dbname AND
t.TABLE_TYPE = "BASE TABLE"
ORDER BY
t.TABLE_NAME ASC
INTO @sql
;
PREPARE stmt FROM @sql;
EXECUTE stmt;
END
Notes:
The SELECT..INTO @sql creates the necessary query, and the PREPARE... EXECUTE runs it.
Sets the group_concat_max_len variable in order to allow a long enough result string from GROUP_CONCAT.
The above procedure is useful for a quick look in an admin environment like Navicat, or on the command line. However, despite returning a result set, so far as I am aware it can't be referenced in another View or Query, presumably because MySQL is unable to determine, before running it, what result sets it produces, let alone what columns they have.
So, it is still useful to be able to quickly produce, without manual editing, the separate SELECT...UNION statement that can be used as a View. That is useful if you want to join the row counts to some other per-table info from another table. Herewith another stored procedure:
BEGIN
# zgwp_tables_rowcounts_view_statement
# Output: SelectStatement
# Outputs a single row and column, containing a (possibly lengthy)
# SELECT...UNION statement that, if used as a View, will output
# TableName RowCount for all tables in the current database.
SET SESSION group_concat_max_len = 1000000;
SET @dbname = DATABASE();
SELECT
GROUP_CONCAT(
CONCAT (
'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ',
table_name, ' ', CHAR(10))
SEPARATOR 'UNION '
) AS SelectStatement
FROM
information_schema.`TABLES` AS t
WHERE
t.TABLE_SCHEMA = @dbname AND
t.TABLE_TYPE = "BASE TABLE"
ORDER BY
t.TABLE_NAME ASC
;
END
Notes
Very similar to the first procedure in concept. I added a linebreak (CHAR(10)) to each subsidiary "SELECT...UNION" statement, for convenience in viewing or editing the statement.
You could create this as a function and return the SelectStatement, if that's more convenient for your environment.
Hope that helps.
How to count rows by some condition in one query?
You can try: select count(*) from table where type is null or type = '';
Exact count of all rows in MySQL database
I think the only accurate (and slower) way is to do for every single table:
SELECT COUNT(*) FROM Table
MYSQL - count number of rows in each table
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<your db>';
I also hope you realise there's an error in your query: it's missing a FROM
.
Count table rows
SELECT COUNT(*) FROM fooTable;
will count the number of rows in the table.
See the reference manual.
How to count rows from two tables in one query?
Use sub-queries and if it needs add FROM DUAL
:
SELECT
(SELECT COUNT(*) FROM TABLE1) As Table1Count,
(SELECT COUNT(*) FROM TABLE2) As Table2Count
[FROM DUAL]
Related Topics
How to Import Excel Files with Different Names and Same Schema into Database
Possible to Restore a Backup of SQL Server 2014 on SQL Server 2012
How to Convert an Int to a Zero Padded String in T-Sql
Using Table Variable with Sp_Executesql
How to Perform a Select Query in a Do Block
Rails Scope to Check If Association Does Not Exist
SQL Server as Statement Aliased Column Within Where Statement
Is There a Performance Difference Between Between and in with MySQL or in SQL in General
Why Do I Need to Explicitly Specify All Columns in a SQL "Group By" Clause - Why Not "Group by *"
Combine Two Tables into a New One So That Select Rows from the Other One Are Ignored
How to Mark Certain Nr of Rows in Table on Concurrent Access
Is There Any Function for Translating Data in SQL
How to Write a Query That Does Something Similar to MySQL's Group_Concat in Oracle
How to Convert a Text Field in an Access Table to a Rich Text Memo Using Vba