Querying multiple databases at once

Question

I have WordPress instances with each in its own database. For an update I need to query all active plugins, that are stored in a table 'wp_options' and accessible via

WHERE option_name='active_plugins' 

How can I access all active plugin settings (spread over multiple databases) and output them in one single SQL result? I know the database.tablename syntax, but how do I go on from there with the above Where statement?

A request in a single database would look like this:

SELECT option_value   FROM `database`.`wp_options`  WHERE option_name="active_plugins" 

Answer 1

SELECT option_value  FROM `database1`.`wp_options`   WHERE option_name="active_plugins" UNION SELECT option_value  FROM `database2`.`wp_options`   WHERE option_name="active_plugins" 

Answer 2

The solution by Pentium10 is good but its drawback is that you have to extend the query for every schema to be included. The below solution uses a prepared statement to produce a result set for all schemas on your MySQL server which have the wp_options table. This should be more convenient for you.

DROP PROCEDURE IF EXISTS `MultipleSchemaQuery`;  DELIMITER $$  CREATE PROCEDURE `MultipleSchemaQuery`() BEGIN     declare scName varchar(250);     declare q varchar(2000);      DROP TABLE IF EXISTS ResultSet;     create temporary table ResultSet (      option_value varchar(200)     );      DROP TABLE IF EXISTS MySchemaNames;     create temporary table MySchemaNames (         schemaName varchar(250)     );      insert into MySchemaNames     SELECT distinct         TABLE_SCHEMA as SchemaName     FROM          `information_schema`.`TABLES`       where          TABLE_NAME = 'wp_options';  label1:     LOOP         set scName = (select schemaName from MySchemaNames limit 1);         set @q = concat('select option_value from ', scName, '.wp_options where option_name=\'active_plugins\'');         PREPARE stmt1 FROM @q;         EXECUTE stmt1;         DEALLOCATE PREPARE stmt1;          delete from MySchemaNames where schemaName = scName;         IF ((select count(*) from MySchemaNames) > 0) THEN             ITERATE label1;         END IF;         LEAVE label1;      END LOOP label1;      SELECT * FROM ResultSet;      DROP TABLE IF EXISTS MySchemaNames;     DROP TABLE IF EXISTS ResultSet; END $$  DELIMITER ;  CALL MultipleSchemaQuery();