Today I Learned

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.

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.

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;