[mysql]특정DB 전체 테이블 삭제

SET @tables = NULL;

SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables

FROM information_schema.tables

WHERE table_schema = '[DB명]';

SET @tables = CONCAT('DROP TABLE ', @tables);

PREPARE stmt FROM @tables;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

특정 테이블만 삭제해야할 경우는 조건문에 아래와 같이 추가하면됩니다.

AND table_name LIKE '%[테이블명]%';

result

SET @tables = NULL
> OK
> Time: 0.003s


SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
FROM information_schema.tables
WHERE table_schema = 'darksharavim'
> OK
> Time: 0.003s


SET @tables = CONCAT('DROP TABLE ', @tables)
> OK
> Time: 0.003s


PREPARE stmt FROM @tables
> OK
> Time: 0.003s


EXECUTE stmt
> OK
> Time: 0.05s


DEALLOCATE PREPARE stmt
> OK
> Time: 0.004s