How to delete multiple databases using MYSQL

When you have to delete 100+ databases created by a botched deployment script you have a few options available:

## 1. Plain SQL
You'll have to run this query and then copy paste the result into a second query.

```sql

SELECT
	GROUP_CONCAT( CONCAT( 'DROP DATABASE `', SCHEMA_NAME, '`;' ) SEPARATOR ' ' ) AS DELETE_QUERY
FROM
	information_schema.SCHEMATA 
WHERE
	SCHEMA_NAME LIKE '{DBNAME}%'

```


## 2. Stored procedure
This allows you to create a stored procedure that will run your query in one go.

```sql
CREATE PROCEDURE clean () BEGIN

	SET @QUERY := (
		SELECT
			CONCAT( 'DROP DATABASE `', SCHEMA_NAME, '`;' ) 
		FROM
			`information_schema`.`SCHEMATA` 
		WHERE
			SCHEMA_NAME LIKE '{DBNAME}%' 
			LIMIT 1 
		);
	PREPARE stmt 
	FROM
		@QUERY;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END;
CALL clean ();
DROP PROCEDURE clean;
```

Zeno Popovici
15 Dec 2020