How to delete all tables from database without permissions to drop database
Seeing the title, you must be wondering what is the need to delete all tables from a database using a MySQL procedure, when you can just drop database from phpmyadmin or cpanel, or run "DROP DATABASE database_name" from MySQL command line.
Well, recently I faced an issue while working on a Drupal project, where on the server there is no cpanel or phpmadmin installed and I did not have permission to drop/create database, but had permissions to create, use and delete tables and procedures. So, instead of requesting for access to delete/create, I decided to write a simple MySQL stored procedure on the database which can be called every time to delete the tables.
The following is a MySQL procedure which has a cursor defined to select table names from the information schema table(a database that stores information about all the databases in the server) and prepares a statement to be executed! The code below is very self explanatory and simple for mysql procedure standards.
/*Deafult delimiter for mysql is ';', but while creating a procedure, this should be changed to execute after all statements have been saved. So the delimiter can be changed to anything of your choice (preferably a symbol that is rarely used: //, $$, etc)*/ DELIMITER $$ DROP PROCEDURE IF EXISTS drop_all_tables$$ CREATE PROCEDURE drop_all_tables(IN databasename CHAR(50)) BEGIN DECLARE no_more_tables INT DEFAULT FALSE; DECLARE tablename CHAR(50); DECLARE cursor1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = databasename; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_tables = TRUE; /* If not set 0 it will throw errors to tables with foreign keys */ SET foreign_key_checks = 0; OPEN cursor1; get_each_table_loop: LOOP FETCH cursor1 INTO tablename; IF no_more_tables THEN LEAVE get_each_table_loop; END IF; SET @query = CONCAT('DROP TABLE ', databasename, '.' , tablename, ';'); PREPARE statement FROM @query; EXECUTE statement; DEALLOCATE PREPARE statement; END LOOP; CLOSE cursor1; SET foreign_key_checks = 1; END$$ /*Change delimiter back to ;*/ DELIMITER ;
Call the procedure after it has been created like this :
CALL drop_all_tables('your database');
This procedure is prepared based on other scripts found on the website after some research. It has been tweaked for general purpose to be used on any database. I faced this problem during migration of a website from Drupal 6 to Drupal 7, where we had to restore the database each time the site crashed. We used the command "gunzip < db_backup_2014-05-28.sql.gz | mysql -u username -ppassword database_name" to restore the database but this always caused errors because the old tables would not get deleted.