Running the same query on multiple databases in one query (MySQL)

Build a query in a string variable and then submit it using MySQL’s prepared statement functionality.  (see: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html)

SELECT GROUP_CONCAT(CONCAT("SELECT FROM `", SCHEMA_NAME, "`.`cms_users` WHERE `email` LIKE '%admin.bilsi%'") SEPARATOR ' UNION ALL ')
  INTO @stmt_sql 
  FROM INFORMATION_SCHEMA.SCHEMATA 
 WHERE SCHEMA_NAME NOT IN('mysql', 'test', 'tmp');
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Leave a Reply

Your email address will not be published. Required fields are marked *