I have a database with 500+ tables, each with identical structure, that contain historical data from sensors. I am trying to come up with a query that will locate, for example, all instances where sensor n exceeds x. The problem is that the tables are dynamic, the query must be able to dynamically obtain the list of tables.

I can query information_schema.tables to get a list of the tables, like so:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'database_name';

I can use this to create a loop in the program and then query the database repeatedly, however it seems like there should be a way to have MySql do the multiple table search.

I have not been able to make a stored procedure that works, but the examples I can find are generally for searching for a string in any column. I want to specifically find data in a specific column that exists in all tables. I admit I do not understand how to properly use stored procedures nor if they are the appropriate solution to this problem.

An example query inside the loop would be:

SELECT device_name, sensor_value
FROM device_table
WHERE sensor_value > 10;

Trying the following does not work:

SELECT device_name, sensor_value
FROM
    (
    SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name'
    )
WHERE sensor_value > 10;

It results in an error: "Every derived table must have its own alias."

The goal is to have a list of all devices that have had a given sensor value occur anywhere in their log (table).

Ultimately, should I just loop in my program once I've obtained a list of tables, or is there a query structure that would be more efficient?