CODEWITHSUNDEEP
mysql
Merlin
Merlin

Reputation: 25629

Drop tables in MySQL

I have a database with tables some examples named "_data", "_keep", "foo", "bar"

Without dropping all tables in Db, How do I drop all tables that dont start with "_". In above example foo and bar.

Using Sql

Having problem with keywords used as table names, is there away to Not to drop "_" but drop any tables older than 10 minutes. (I wanted to drop before table creation. now looking to do drop after update.)

Upvotes: 1

Views: 974

Answers (4)

Nicola Cossu
Nicola Cossu

Reputation: 56357

set @str = (select concat('drop table ', group_concat(table_name separator ','),';')
from information_schema.tables
where table_schema = 'your_database_name' and table_name not regexp '^_');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;

edit. I answer you here because it's more readable. If you want to retrieve only tables created more than 10 minutes ago you have to do

select table_name from information_schema.tables
where table_schema = 'your_db'
and now() - interval 10 minute > create_time

Apply where clause to my previous code. I hope this is what you're looking for.

Upvotes: 4

Geeklab
Geeklab

Reputation: 199

The following line:

mysqldump -u [username] -p [password] --add-drop-table --no-data [databasename]|grep ^DROP

produces a list of all tables, like this:

DROP TABLE IF EXISTS `agenda`;
DROP TABLE IF EXISTS `bla`;
DROP TABLE IF EXISTS `test`;
DROP TABLE IF EXISTS `users`;

Filter that list with "grep" and pipe it back into mysql.

mysqldump -u [username] -p [password] --add-drop-table --no-data [databasename] | \
    grep -E "^DROP TABLE IF EXISTS .[^_]" | mysql -u [username] -p [password] [databasename]

Upvotes: 1

David Machel
David Machel

Reputation: 296

delimiter $$
create procedure drop_tables_like(pattern varchar(255), db varchar(255))
begin
select @str_sql:=concat('drop table ', group_concat(table_name))
from information_schema.tables
where table_schema=db and table_name like pattern;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

call drop_tables_like('_%', 'db_1')$$

drop procedure if exists drop_tables_like$$
delimiter ;

Upvotes: 4

Glen Solsberry
Glen Solsberry

Reputation: 12320

Check out mk-find. It will allow you to run commands against your server, and, based on the results of those commands, run other commands.

Upvotes: 0

Related Questions