MySQL Cross-Database Commands

mysql-cross-db-command.md

Generate UNION ALL queries for e.g create view

SELECT CONCAT('SELECT `type` COLLATE utf8mb4_unicode_ci as type, "', schema_name, '" COLLATE utf8mb4_unicode_ci as tenant FROM `', 
    schema_name, '`.`micro_sessions` UNION ALL')
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql','performance_schema');

Convert everything to different Charset + Collation

SELECT CONCAT(
	'ALTER TABLE `', 
	TABLE_NAME,
	'` CONVERT TO CHARACTER SET utf8mb4',
	' COLLATE utf8mb4_unicode_ci;')
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA IN (
	SELECT schema_name 
	FROM information_schema.schemata 
	WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')
) AND TABLE_TYPE="BASE TABLE";