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
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.
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.
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).
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.
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With