Ransford Okpoti's Blog

5 February, 2012

Dropping All Tables In A MySQL Database Using A Stored Procedure

Filed under: MySQL — ranskills @ 1:33 pm

Using an engine like InnoDB, which enforces referential integrity through the use of foreign keys, poses a little problem when deleting a table whose field(s) act as foreign keys in other table(s). Dropping an entire database may seem like a convenient approach to use, but will not be an option to users who do not have database creation privileges on the database server, and for those who would want to preserve other objects in the database like views, functions, stored prodecures, etc.

Deleting interconnected tables in a database can be a little frustrating, but the solution is that simple, suppress foreign key checks, if necessary, and delete the tables of interest.
Implementing a solution in the database through the use of a stored procedure is show below.

DROP PROCEDURE IF EXISTS procDropAllTables $$

CREATE PROCEDURE procDropAllTables()

BEGIN
		DECLARE table_name VARCHAR(255);
		DECLARE end_of_tables INT DEFAULT 0;

		DECLARE cur CURSOR FOR 
			SELECT t.table_name 
			FROM information_schema.tables t 
			WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE';
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;

		SET FOREIGN_KEY_CHECKS = 0;
		OPEN cur;

		tables_loop: LOOP
			FETCH cur INTO table_name;

			IF end_of_tables = 1 THEN
				LEAVE tables_loop;
			END IF;

			SET @s = CONCAT('DROP TABLE IF EXISTS ' , table_name);
			PREPARE stmt FROM @s;
			EXECUTE stmt;

		END LOOP;
	
		CLOSE cur;
		SET FOREIGN_KEY_CHECKS = 1;
	END

Now, let’s drop all the tables by calling our stored procedure as show below.

CALL procDropAllTables();

The above procedure can be tweaked if table deletion is not desired, but should be rather emptied by changing the highlighted line to

SET @s = CONCAT('DELETE FROM ' , table_name);

This is only one of the approaches that could be used to solve the problem, but they all basically work on the same premise that foreign key checks MUST BE disabled.

Advertisements

2 Comments »

  1. Excellent procedure, thanks a lot! Note that in phpmyadmin running this gives a “can’t return a result set in the given context” error.
    But running it from the command line works … weird, I know …

    Comment by Gert — 19 November, 2012 @ 3:50 am

  2. Your posting really sttiaghrened me out. Thanks!

    Comment by Cassara — 29 December, 2014 @ 5:47 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: