I'm currently using the script
SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Tables'; However, it's not accurate, because the engine used by my MySQL tables is InnoDB (I only realised this could be an issue now, be these databases have existed for a while).
Is there any way to get an exact count of every row in every table of a database with MySQL?
Cheers.
I think the only accurate (and slower) way is to do for every single table:
SELECT COUNT(*) FROM Table 
SELECT CONCAT('SELECT COUNT(*) FROM ', table_name) FROM information_schema.tables WHERE table_schema = 'Tables';
This SQL statement will generate a union query that can complete the task in one step:
SELECT CONCAT('SELECT \'', table_name ,'\' as tbl, (SELECT COUNT(*) FROM ', table_name, ') AS ct UNION') FROM information_schema.tables WHERE table_schema = 'your_database'; After running the SQL statement, just paste the output into a text editor, delete the word "UNION" from the end of the output, and you will have the union query.
Beware that running the union query could take a lot of resources depending on your database size, load, and hardware, so you may not want to run it all at once.
I was having the same problem which led me to this question.
Thanks to computerGuy12345's answer, I came up with a stored procedure that does this without having to copy-paste. As he said, beware that this will scan each table and might take a while.
DELIMITER $$ CREATE PROCEDURE `all_tables_rowcount`(databaseName VARCHAR(250)) BEGIN DECLARE p_done INT DEFAULT FALSE; DECLARE p_queryString varchar(250) ; DECLARE p_cur CURSOR FOR SELECT queryString FROM tmp.tableCountQueries; DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_done = TRUE; DROP TEMPORARY TABLE IF EXISTS tmp.tableCountQueries; DROP TEMPORARY TABLE IF EXISTS tmp.tableCounts; CREATE TEMPORARY TABLE tmp.tableCounts(TableName varchar(250), RowCount BIGINT) ; CREATE TEMPORARY TABLE tmp.tableCountQueries(queryString varchar(250)) AS ( SELECT CONCAT( 'INSERT INTO tmp.tableCounts(TableName, RowCount) SELECT "', table_name, '", COUNT(*) AS RowCount FROM ', table_schema, '.', table_name ) AS queryString FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = databaseName ) ; OPEN p_cur; read_loop: LOOP FETCH p_cur INTO p_queryString; IF p_done THEN LEAVE read_loop; END IF; SET @queryString = p_queryString ; PREPARE rowcountTable FROM @queryString; EXECUTE rowcountTable ; END LOOP; SELECT * FROM tmp.tableCounts ; END Select Sum(column_Name) from table ,can not give the exact count of rows in a table , it wil give total row count+1 , wil give the next data inserting row also. and one more thing is, in sum(Column_Name) the column_Name should be int ,if it is varchar or char sum function wont work. soo the best thing is use Select Count(*) from table to get exact number of rows in a table.
ThnQ,
Venkat
select table_name,table_rows from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='database name'; table_name means number of tables which is present in "data base "
table_rows means number rows in each column
table_schema is the database name. from which data base you are looking for the counts
this worked for me
<?php mysql_connect("localhost", "root", "pass") or die(mysql_error()); mysql_select_db("csl") or die(mysql_error()); $dtb=mysql_query("SHOW TABLES") or die (mysql_error()); $jmltbl=0; $jml_record=0; $jml_record=0; $total =0; while($row=mysql_fetch_array($dtb)) { $sql=mysql_query("select * from $row[0]"); $jml_record=mysql_num_rows($sql); $total += $jml_record; // this counts all the rows $jmltbl++; $jml_record+=$jml_record; } // echo"--------$jmltbl Tables, $jml_record records."; // will print count of indivual table echo $total; // this will print you count of all rows in MySQL database ?> i have tested this code and its works
output 1145 for me
count(*) instead of *.