Syntax error How to use the name of the current database to delete it in MySQL?

How to use the name of the current database to delete it in MySQL?

To get the current database, you can use the SELECT DATABASE() −

select database();

Following is the syntax −

set @anyVariableName = database();
select @anyVariableName;
set @anyVariableName2 = concat('drop database ', @yourVariableName);
prepare anyVariableName3 from @yourVariableName2;
execute yourVariableName3;

Let us execute the above query in order to get current database and delete it −

mysql> set @currentDatabase = database();
Query OK, 0 rows affected (0.00 sec)

mysql> select @currentDatabase;
+------------------+
| @currentDatabase |
+------------------+
| employeeonboard  |
+------------------+
1 row in set (0.00 sec)

mysql> set @sqlQuery = concat('drop database ', @currentDatabase);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @sqlQuery;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> execute stmt;
Query OK, 0 rows affected (0.56 sec)

In order to check whether the database exists now, use the SELECT DATABASE() −

mysql> select database();

This will produce the following output −

+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
Updated on: 2020-11-19T12:28:58+05:30

100 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements