Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apply mysql query to each table in a database

Tags:

mysql

is there a way to apply a query to each table in a mysql database?

Something like

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
like image 510
scravy Avatar asked Dec 06 '11 11:12

scravy


People also ask

How do I make multiple tables into one query?

To create a multi-table query: Select the Query Design command from the Create tab on the Ribbon. In the dialog box that appears, select each table you want to include in your query and click Add. You can press and hold the Ctrl key on your keyboard to select more than one table.

How do I query a table in MySQL?

To get a list of the tables in a MySQL database, use the mysql client tool to connect to the MySQL server and run the SHOW TABLES command. The optional FULL modifier will show the table type as a second output column.

How can I get result from two tables in MySQL?

Ans: Joining two tables in SQL can be done in four major ways: Inner Join (returns rows with matching columns), Left Join (ALL records in the left table and matching records in the right table), Right Join (ALL records in the right table and matching records in the left table), and Union (removes duplicates).

Which MySQL statements can you use to populate a single database table with data from a CSV file?

If the CSV file is located on the local machine, you can use the LOAD DATA LOCAL INFILE statement instead. INTO TABLE – This indicates the destination table you are importing the CSV file into. Change the table_name to the name of your table.


1 Answers

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

beware this is just an approximate value

In order 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 table to 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');
like image 147
Nicola Cossu Avatar answered Sep 19 '22 21:09

Nicola Cossu