HR

How to delete all tables from database without permissions to drop database

Abhai.sasidharan | | 2 min read

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.

 

 

Related Topics