Applying mysql query to every table in database

Question

Is there a way to apply a query to every table in mysql database?

Sort of

SELECT count(*) FROM {ALL TABLES}
-- gives the number of count(*) in each Table

and

DELETE FROM {ALL TABLES}
-- Like DELETE FROM TABLE applied on each Table

Answer 1

select sum(table_rows) as total_rows
from information_schema.tables
where table_schema = 'your_db_name'

Beware, this is just an estimate.

To delete the contents of all your tables, you can do something like this

select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db_name'

Then run the output of this query.

UPDATE

This is a stored procedure to apply truncate tableto all tables in a specific database.

delimiter //
drop procedure if exists delete_contents //
create procedure delete_contents (in db_name varchar(100))
begin
declare finish int default 0;
declare tab varchar(100);
declare cur_tables cursor for select table_name from information_schema.tables where table_schema = db_name and table_type = 'base table';
declare continue handler for not found set finish = 1;
open cur_tables;
my_loop:loop
fetch cur_tables into tab;
if finish = 1 then
leave my_loop;
end if;

set @str = concat('truncate ', tab);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end loop;
close cur_tables;
end; //
delimiter ;

call delete_contents('your_db_name');

Answer 2

if tables are linked by any field, you can use a table alias, for example

select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3

similar,

delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3

You can include the conditions according to your requirements.

If tables are not relevant, use below

SELECT
  (SELECT COUNT(*) FROM table1 WHERE someCondition) as count1, 
  (SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
  (SELECT COUNT(*) FROM table3 WHERE someCondition) as count3

where, .

:

| count1 | count2 | count3 |

| 50 | 36 | 21 |