I just run:
show table status;
This will give you the row count for EVERY table plus a bunch of other info. I used to use the selected answer above, but this is much easier.
I'm not sure if this works with all versions, but I'm using 5.5 with InnoDB engine.
This stored procedure lists tables, counts records, and produces a total number of records at the end.
To run it after adding this procedure:
CALL `COUNT_ALL_RECORDS_BY_TABLE` ();
-
The Procedure:
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE TNAME CHAR(255);
DECLARE table_names CURSOR for
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN table_names;
DROP TABLE IF EXISTS TCOUNTS;
CREATE TEMPORARY TABLE TCOUNTS
(
TABLE_NAME CHAR(255),
RECORD_COUNT INT
) ENGINE = MEMORY;
WHILE done = 0 DO
FETCH NEXT FROM table_names INTO TNAME;
IF done = 0 THEN
SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")");
PREPARE stmt_name FROM @SQL_TXT;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;
END IF;
END WHILE;
CLOSE table_names;
SELECT * FROM TCOUNTS;
SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS;
END
The following query produces a(nother) query that will get the value of count(*) for every table, from every schema, listed in information_schema.tables. The entire result of the query shown here - all rows taken together - comprise a valid SQL statement ending in a semicolon - no dangling 'union'. The dangling union is avoided by use of a union in the query below.
select concat('select "', table_schema, '.', table_name, '" as `schema.table`,
count(*)
from ', table_schema, '.', table_name, ' union ') as 'Query Row'
from information_schema.tables
union
select '(select null, null limit 0);';
One more option: for non InnoDB it uses data from information_schema.TABLES (as it's faster), for InnoDB - select count(*) to get the accurate count. Also it ignores views.
SET @table_schema = DATABASE();
-- or SET @table_schema = 'my_db_name';
SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;
SELECT GROUP_CONCAT(
'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`'
SEPARATOR '\nUNION\n') INTO @selects
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @table_schema
AND ENGINE = 'InnoDB'
AND TABLE_TYPE = "BASE TABLE";
SELECT CONCAT_WS('\nUNION\n',
CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'),
@selects) INTO @selects;
PREPARE stmt FROM @selects;
EXECUTE stmt USING @table_schema;
DEALLOCATE PREPARE stmt;
If your database has a lot of big InnoDB tables counting all rows can take more time.
There's a bit of a hack/workaround to this estimate problem.
Auto_Increment - for some reason this returns a much more accurate row count for your database if you have auto increment set up on tables.
Found this when exploring why show table info did not match up with the actual data.
SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
SUM(TABLE_ROWS) AS DBRows,
SUM(AUTO_INCREMENT) AS DBAutoIncCount
FROM information_schema.tables
GROUP BY table_schema;
+--------------------+-----------+---------+----------------+
| Database | DBSize | DBRows | DBAutoIncCount |
+--------------------+-----------+---------+----------------+
| Core | 35241984 | 76057 | 8341 |
| information_schema | 163840 | NULL | NULL |
| jspServ | 49152 | 11 | 856 |
| mysql | 7069265 | 30023 | 1 |
| net_snmp | 47415296 | 95123 | 324 |
| performance_schema | 0 | 1395326 | NULL |
| sys | 16384 | 6 | NULL |
| WebCal | 655360 | 2809 | NULL |
| WxObs | 494256128 | 530533 | 3066752 |
+--------------------+-----------+---------+----------------+
9 rows in set (0.40 sec)
You could then easily use PHP or whatever to return the max of the 2 data columns to give the "best estimate" for row count.
i.e.
SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRows
FROM information_schema.tables
GROUP BY table_schema;
Auto Increment will always be +1 * (table count) rows off, but even with 4,000 tables and 3 million rows, that's 99.9% accurate. Much better than the estimated rows.
The beauty of this is that the row counts returned in performance_schema are erased for you, as well, because greatest does not work on nulls. This may be an issue if you have no tables with auto increment, though.
You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.
To get rows per table, you can use a query like this:
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';
This is how I count TABLES and ALL RECORDS using PHP:
$dtb = mysql_query("SHOW TABLES") or die (mysql_error());
$jmltbl = 0;
$jml_record = 0;
$jml_record = 0;
while ($row = mysql_fetch_array($dtb)) {
$sql1 = mysql_query("SELECT * FROM " . $row[0]);
$jml_record = mysql_num_rows($sql1);
echo "Table: " . $row[0] . ": " . $jml_record record . "<br>";
$jmltbl++;
$jml_record += $jml_record;
}
echo "--------------------------------<br>$jmltbl Tables, $jml_record > records.";
SELECT TABLE_NAME,SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
GROUP BY TABLE_NAME;
That's all you need.
The following query produces a(nother) query that will get the value of count(*) for every table, from every schema, listed in information_schema.tables. The entire result of the query shown here - all rows taken together - comprise a valid SQL statement ending in a semicolon - no dangling 'union'. The dangling union is avoided by use of a union in the query below.
select concat('select "', table_schema, '.', table_name, '" as `schema.table`,
count(*)
from ', table_schema, '.', table_name, ' union ') as 'Query Row'
from information_schema.tables
union
select '(select null, null limit 0);';
One more option: for non InnoDB it uses data from information_schema.TABLES (as it's faster), for InnoDB - select count(*) to get the accurate count. Also it ignores views.
SET @table_schema = DATABASE();
-- or SET @table_schema = 'my_db_name';
SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;
SELECT GROUP_CONCAT(
'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`'
SEPARATOR '\nUNION\n') INTO @selects
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @table_schema
AND ENGINE = 'InnoDB'
AND TABLE_TYPE = "BASE TABLE";
SELECT CONCAT_WS('\nUNION\n',
CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'),
@selects) INTO @selects;
PREPARE stmt FROM @selects;
EXECUTE stmt USING @table_schema;
DEALLOCATE PREPARE stmt;
If your database has a lot of big InnoDB tables counting all rows can take more time.
The code below generation the select query for all tales. Just delete last "UNION ALL" select all result and paste a new query window to run.
SELECT
concat('select ''', table_name ,''' as TableName, COUNT(*) as RowCount from ' , table_name , ' UNION ALL ') as TR FROM
information_schema.tables where
table_schema = 'Database Name'
Simple way:
SELECT
TABLE_NAME, SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{Your_DB}'
GROUP BY TABLE_NAME;
Result example:
+----------------+-----------------+
| TABLE_NAME | SUM(TABLE_ROWS) |
+----------------+-----------------+
| calls | 7533 |
| courses | 179 |
| course_modules | 298 |
| departments | 58 |
| faculties | 236 |
| modules | 169 |
| searches | 25423 |
| sections | 532 |
| universities | 57 |
| users | 10293 |
+----------------+-----------------+
You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.
To get rows per table, you can use a query like this:
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';
This is what I do to get the actual count (no using the schema)
It's slower but more accurate.
It's a two step process at
Get list of tables for your db. You can get it using
mysql -uroot -p mydb -e "show tables"
Create and assign the list of tables to the array variable in this bash script (separated by a single space just like in the code below)
array=( table1 table2 table3 )
for i in "${array[@]}"
do
echo $i
mysql -uroot mydb -e "select count(*) from $i"
done
Run it:
chmod +x script.sh; ./script.sh
If you use the database information_schema, you can use this mysql code (the where part makes the query not show tables that have a null value for rows):
SELECT TABLE_NAME, TABLE_ROWS
FROM `TABLES`
WHERE `TABLE_ROWS` >=0
Like @Venkatramanan and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. Here's a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff.
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(*) AS exact_row_count FROM `',
table_schema,
'`.`',
table_name,
'` UNION '
)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '**my_schema**';
It produces output like this:
SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION
SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION
SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION
SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION
SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION
SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION
SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION
SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION
Copy and paste except for the last UNION to get nice output like,
+------------------+-----------------+
| table_name | exact_row_count |
+------------------+-----------------+
| func | 0 |
| general_log | 0 |
| help_category | 37 |
| help_keyword | 450 |
| help_relation | 990 |
| help_topic | 504 |
| host | 0 |
| ndb_binlog_index | 0 |
+------------------+-----------------+
8 rows in set (0.01 sec)
If you know the number of tables and their names, and assuming they each have primary keys, you can use a cross join in combination with COUNT(distinct [column]) to get the rows that come from each table:
SELECT
COUNT(distinct t1.id) +
COUNT(distinct t2.id) +
COUNT(distinct t3.id) AS totalRows
FROM firstTable t1, secondTable t2, thirdTable t3;
Here is an SQL Fiddle example.
I just run:
show table status;
This will give you the row count for EVERY table plus a bunch of other info. I used to use the selected answer above, but this is much easier.
I'm not sure if this works with all versions, but I'm using 5.5 with InnoDB engine.
Based on @Nathan's answer above, but without needing to "remove the final union" and with the option to sort the output, I use the following SQL. It generates another SQL statement which then just run:
select CONCAT( 'select * from (\n', group_concat( single_select SEPARATOR ' UNION\n'), '\n ) Q order by Q.exact_row_count desc') as sql_query
from (
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(1) AS exact_row_count
FROM `',
table_schema,
'`.`',
table_name,
'`'
) as single_select
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA_NAME'
and table_type = 'BASE TABLE'
) Q
You do need a sufficiently large value of group_concat_max_len server variable but from MariaDb 10.2.4 it should default to 1M.
Poster wanted row counts without counting, but didn't specify which table engine. With InnoDB, I only know one way, which is to count.
This is how I pick my potatoes:
# Put this function in your bash and call with:
# rowpicker DBUSER DBPASS DBNAME [TABLEPATTERN]
function rowpicker() {
UN=$1
PW=$2
DB=$3
if [ ! -z "$4" ]; then
PAT="LIKE '$4'"
tot=-2
else
PAT=""
tot=-1
fi
for t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"`;do
if [ $tot -lt 0 ]; then
echo "Skipping $t";
let "tot += 1";
else
c=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"`;
c=`echo $c | cut -d " " -f 2`;
echo "$t: $c";
let "tot += c";
fi;
done;
echo "total rows: $tot"
}
I am making no assertions about this other than that this is a really ugly but effective way to get how many rows exist in each table in the database regardless of table engine and without having to have permission to install stored procedures, and without needing to install ruby or php. Yes, its rusty. Yes it counts. count(*) is accurate.
If you want the exact numbers, use the following ruby script. You need Ruby and RubyGems.
Install following Gems:
$> gem install dbi
$> gem install dbd-mysql
File: count_table_records.rb
require 'rubygems'
require 'dbi'
db_handler = DBI.connect('DBI:Mysql:database_name:localhost', 'username', 'password')
# Collect all Tables
sql_1 = db_handler.prepare('SHOW tables;')
sql_1.execute
tables = sql_1.map { |row| row[0]}
sql_1.finish
tables.each do |table_name|
sql_2 = db_handler.prepare("SELECT count(*) FROM #{table_name};")
sql_2.execute
sql_2.each do |row|
puts "Table #{table_name} has #{row[0]} rows."
end
sql_2.finish
end
db_handler.disconnect
Go back to the command-line:
$> ruby count_table_records.rb
Output:
Table users has 7328974 rows.
SELECT TABLE_NAME,SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
GROUP BY TABLE_NAME;
That's all you need.
You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.
To get rows per table, you can use a query like this:
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';
If you want the exact numbers, use the following ruby script. You need Ruby and RubyGems.
Install following Gems:
$> gem install dbi
$> gem install dbd-mysql
File: count_table_records.rb
require 'rubygems'
require 'dbi'
db_handler = DBI.connect('DBI:Mysql:database_name:localhost', 'username', 'password')
# Collect all Tables
sql_1 = db_handler.prepare('SHOW tables;')
sql_1.execute
tables = sql_1.map { |row| row[0]}
sql_1.finish
tables.each do |table_name|
sql_2 = db_handler.prepare("SELECT count(*) FROM #{table_name};")
sql_2.execute
sql_2.each do |row|
puts "Table #{table_name} has #{row[0]} rows."
end
sql_2.finish
end
db_handler.disconnect
Go back to the command-line:
$> ruby count_table_records.rb
Output:
Table users has 7328974 rows.
Like @Venkatramanan and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. Here's a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff.
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(*) AS exact_row_count FROM `',
table_schema,
'`.`',
table_name,
'` UNION '
)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '**my_schema**';
It produces output like this:
SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION
SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION
SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION
SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION
SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION
SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION
SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION
SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION
Copy and paste except for the last UNION to get nice output like,
+------------------+-----------------+
| table_name | exact_row_count |
+------------------+-----------------+
| func | 0 |
| general_log | 0 |
| help_category | 37 |
| help_keyword | 450 |
| help_relation | 990 |
| help_topic | 504 |
| host | 0 |
| ndb_binlog_index | 0 |
+------------------+-----------------+
8 rows in set (0.01 sec)
The code below generation the select query for all tales. Just delete last "UNION ALL" select all result and paste a new query window to run.
SELECT
concat('select ''', table_name ,''' as TableName, COUNT(*) as RowCount from ' , table_name , ' UNION ALL ') as TR FROM
information_schema.tables where
table_schema = 'Database Name'
If you know the number of tables and their names, and assuming they each have primary keys, you can use a cross join in combination with COUNT(distinct [column]) to get the rows that come from each table:
SELECT
COUNT(distinct t1.id) +
COUNT(distinct t2.id) +
COUNT(distinct t3.id) AS totalRows
FROM firstTable t1, secondTable t2, thirdTable t3;
Here is an SQL Fiddle example.
Poster wanted row counts without counting, but didn't specify which table engine. With InnoDB, I only know one way, which is to count.
This is how I pick my potatoes:
# Put this function in your bash and call with:
# rowpicker DBUSER DBPASS DBNAME [TABLEPATTERN]
function rowpicker() {
UN=$1
PW=$2
DB=$3
if [ ! -z "$4" ]; then
PAT="LIKE '$4'"
tot=-2
else
PAT=""
tot=-1
fi
for t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"`;do
if [ $tot -lt 0 ]; then
echo "Skipping $t";
let "tot += 1";
else
c=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"`;
c=`echo $c | cut -d " " -f 2`;
echo "$t: $c";
let "tot += c";
fi;
done;
echo "total rows: $tot"
}
I am making no assertions about this other than that this is a really ugly but effective way to get how many rows exist in each table in the database regardless of table engine and without having to have permission to install stored procedures, and without needing to install ruby or php. Yes, its rusty. Yes it counts. count(*) is accurate.
Simple way:
SELECT
TABLE_NAME, SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{Your_DB}'
GROUP BY TABLE_NAME;
Result example:
+----------------+-----------------+
| TABLE_NAME | SUM(TABLE_ROWS) |
+----------------+-----------------+
| calls | 7533 |
| courses | 179 |
| course_modules | 298 |
| departments | 58 |
| faculties | 236 |
| modules | 169 |
| searches | 25423 |
| sections | 532 |
| universities | 57 |
| users | 10293 |
+----------------+-----------------+
This is what I do to get the actual count (no using the schema)
It's slower but more accurate.
It's a two step process at
Get list of tables for your db. You can get it using
mysql -uroot -p mydb -e "show tables"
Create and assign the list of tables to the array variable in this bash script (separated by a single space just like in the code below)
array=( table1 table2 table3 )
for i in "${array[@]}"
do
echo $i
mysql -uroot mydb -e "select count(*) from $i"
done
Run it:
chmod +x script.sh; ./script.sh
You can try this. It is working fine for me.
SELECT IFNULL(table_schema,'Total') "Database",TableCount
FROM (SELECT COUNT(1) TableCount,table_schema
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
GROUP BY table_schema WITH ROLLUP) A;
You can try this. It is working fine for me.
SELECT IFNULL(table_schema,'Total') "Database",TableCount
FROM (SELECT COUNT(1) TableCount,table_schema
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
GROUP BY table_schema WITH ROLLUP) A;
Based on @Nathan's answer above, but without needing to "remove the final union" and with the option to sort the output, I use the following SQL. It generates another SQL statement which then just run:
select CONCAT( 'select * from (\n', group_concat( single_select SEPARATOR ' UNION\n'), '\n ) Q order by Q.exact_row_count desc') as sql_query
from (
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(1) AS exact_row_count
FROM `',
table_schema,
'`.`',
table_name,
'`'
) as single_select
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA_NAME'
and table_type = 'BASE TABLE'
) Q
You do need a sufficiently large value of group_concat_max_len server variable but from MariaDb 10.2.4 it should default to 1M.
If you use the database information_schema, you can use this mysql code (the where part makes the query not show tables that have a null value for rows):
SELECT TABLE_NAME, TABLE_ROWS
FROM `TABLES`
WHERE `TABLE_ROWS` >=0
This stored procedure lists tables, counts records, and produces a total number of records at the end.
To run it after adding this procedure:
CALL `COUNT_ALL_RECORDS_BY_TABLE` ();
-
The Procedure:
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE TNAME CHAR(255);
DECLARE table_names CURSOR for
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN table_names;
DROP TABLE IF EXISTS TCOUNTS;
CREATE TEMPORARY TABLE TCOUNTS
(
TABLE_NAME CHAR(255),
RECORD_COUNT INT
) ENGINE = MEMORY;
WHILE done = 0 DO
FETCH NEXT FROM table_names INTO TNAME;
IF done = 0 THEN
SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")");
PREPARE stmt_name FROM @SQL_TXT;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;
END IF;
END WHILE;
CLOSE table_names;
SELECT * FROM TCOUNTS;
SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS;
END
This is how I count TABLES and ALL RECORDS using PHP:
$dtb = mysql_query("SHOW TABLES") or die (mysql_error());
$jmltbl = 0;
$jml_record = 0;
$jml_record = 0;
while ($row = mysql_fetch_array($dtb)) {
$sql1 = mysql_query("SELECT * FROM " . $row[0]);
$jml_record = mysql_num_rows($sql1);
echo "Table: " . $row[0] . ": " . $jml_record record . "<br>";
$jmltbl++;
$jml_record += $jml_record;
}
echo "--------------------------------<br>$jmltbl Tables, $jml_record > records.";
You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.
To get rows per table, you can use a query like this:
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';
There's a bit of a hack/workaround to this estimate problem.
Auto_Increment - for some reason this returns a much more accurate row count for your database if you have auto increment set up on tables.
Found this when exploring why show table info did not match up with the actual data.
SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
SUM(TABLE_ROWS) AS DBRows,
SUM(AUTO_INCREMENT) AS DBAutoIncCount
FROM information_schema.tables
GROUP BY table_schema;
+--------------------+-----------+---------+----------------+
| Database | DBSize | DBRows | DBAutoIncCount |
+--------------------+-----------+---------+----------------+
| Core | 35241984 | 76057 | 8341 |
| information_schema | 163840 | NULL | NULL |
| jspServ | 49152 | 11 | 856 |
| mysql | 7069265 | 30023 | 1 |
| net_snmp | 47415296 | 95123 | 324 |
| performance_schema | 0 | 1395326 | NULL |
| sys | 16384 | 6 | NULL |
| WebCal | 655360 | 2809 | NULL |
| WxObs | 494256128 | 530533 | 3066752 |
+--------------------+-----------+---------+----------------+
9 rows in set (0.40 sec)
You could then easily use PHP or whatever to return the max of the 2 data columns to give the "best estimate" for row count.
i.e.
SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRows
FROM information_schema.tables
GROUP BY table_schema;
Auto Increment will always be +1 * (table count) rows off, but even with 4,000 tables and 3 million rows, that's 99.9% accurate. Much better than the estimated rows.
The beauty of this is that the row counts returned in performance_schema are erased for you, as well, because greatest does not work on nulls. This may be an issue if you have no tables with auto increment, though.
Source: Stackoverflow.com