Dropping All Tables In A MySQL Database Using A Stored Procedure

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.

✔ Read More...

I guess you came to this post by searching similar kind of issues in any of the search engine and hope that this resolved your problem. If you find this tips useful, just drop a line below and share the link to others and who knows they might find it useful too.

Stay tuned to my blog, twitter or facebook to read more articles, tutorials, news, tips & tricks on various technology fields. Also Subscribe to our Newsletter with your Email ID to keep you updated on latest posts. We will send newsletter to your registered email address. We will not share your email address to anybody as we respect privacy.

This article is related to

MySQL