CODEWITHSUNDEEP
mysqlmysql-workbench
user3930853
user3930853

Reputation: 31

Mysql - Drop table if exists not working

I try to use the following code to empty a database (delete all tables) :

SELECT concat('DROP TABLE IF EXISTS ','`',table_schema,'`','.','`',table_name,'`',';') FROM information_schema.tables WHERE table_schema = 'DB';

I am getting an output of the commands, but nothing happens to the database. If I take an individual command from the output, and run it in the console, it works.

What am I doing wrong?

Upvotes: 2

Views: 7061

Answers (2)

Waqas Shahid
Waqas Shahid

Reputation: 1060

Why you're using SELECT statement, Simply try this query:

DROP TABLE IF EXISTS table_name;

Upvotes: 2

type_outcast
type_outcast

Reputation: 635

As you know, SELECT only returns the result of a query. It doesn't know you actually intend to execute the result of that query. (In most cases, that would make no sense.) You can use prepared statements to do what you want (untested):

SET @s:='';
SELECT @s:=concat(@s, 'DROP TABLE IF EXISTS ','`',table_schema,'`','.','`',table_name,'`',';') FROM information_schema.tables WHERE table_schema = 'DB';   
PREPARE stm FROM @s;
EXECUTE stm;

DEALLOCATE PREPARE stm;

Upvotes: 3

Related Questions