CODEWITHSUNDEEP
mysqlsql
Remi Guan
Remi Guan

Reputation: 22282

Drop a database if there is no table in it

Is there a MySQL command can do something like:

If there is nothing in a database, drop it. If there is any tables in it, do nothing.

Such as:

drop database if exists foobar

But:

drop database if empty foobar

Is there any way to do this?

Upvotes: 1

Views: 674

Answers (2)

MAK
MAK

Reputation: 7260

As Barmar said you can use INFORMATION_SCHEMA.TABLES with stored procedure.

Here is my small effort:

DELIMITER //
CREATE PROCEDURE spDropDB_IF_Empty()
BEGIN

IF (SELECT COUNT(table_name) from INFORMATION_SCHEMA.tables
    WHERE Table_Schema = 'mariadb')= 0 THEN

    DROP DATABASE mariadb;

ELSE
    SELECT 'There are tables in the mariaDB';
END IF;
END //
DELIMITER ;

Call SP:

CALL spDropDB_IF_Empty()

Upvotes: 2

zedfoxus
zedfoxus

Reputation: 37039

Hopefully this will help others as well. I just created a procedure for my own purpose after reading your question and commentators' comments.

use mysql;

-- switch to a different delimiter
delimiter $$

create procedure drop_empty_databases()
begin

    declare table_schema varchar(200); -- will hold schema obtained from query
    declare schema_end int default 0;  -- temp variable that's set to 1 when reading of all schema is done

    -- cursor that lists all schemas with no tables
    declare cur cursor for 
        select s.schema_name
        from information_schema.schemata s
        left join information_schema.tables t on t.table_schema = s.schema_name
        group by s.schema_name
        having count(t.table_name) = 0;

    -- set schema_end to 1 when we run out of schemas while looping
    declare continue handler for not found set schema_end = 1;

    open cur;
    looper: loop
        fetch cur into table_schema;
        if schema_end = 1 then
            leave looper;
        end if;

        set @sql = concat('drop database ', table_schema);
        prepare stmt from @sql;
        execute stmt;
    end loop;
    close cur;

end
$$

-- switch back to semi-colon delimiter
delimiter ;

Usage:

use mysql;
create database test123;
call drop_empty_databases();  -- test123 database should be gone after running this

Please test this on a non-production server and confirm that it does what you want it to do.

Upvotes: 1

Related Questions