How to run the same query against multiple tables in the database

Question

While running following query MYSQL complains that : Table 'DB.tableName' doesn't exist.

CREATE PROCEDURE CountSignatures()
  BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE signatureCount INT;
     DECLARE tableName CHAR(100);
     DECLARE tableList CURSOR FOR Select table_name from information_schema.tables where table_name like "%FAULT_20150320%";
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     SET signatureCount = 1;
     OPEN tableList;
     tableListLoop: LOOP
       SET done = FALSE ;
       FETCH tableList INTO tableName;
       IF done THEN
         LEAVE tableListLoop;
       END IF;

     **Select count(distinct signature) from tableName;**

     END LOOP;
     CLOSE tableList;
  END$$

While if I use the following query then tableName variable value is printed correctly :

CREATE PROCEDURE CountSignatures()
  BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE signatureCount INT;
     DECLARE tableName CHAR(100);
     DECLARE tableList CURSOR FOR Select table_name from information_schema.tables where table_schema="LogData" and table_name like "%FAULT_20150320%";
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     SET signatureCount = 1;
     OPEN tableList;
     tableListLoop: LOOP
       SET done = FALSE ;
       FETCH tableList INTO tableName;
       IF done THEN
         LEAVE tableListLoop;
       END IF;

       **Select tableName;**

     END LOOP;
     CLOSE tableList;
  END$$

Answer 1

The FROM part of the SELECT statement has to have actual table names, not a CHAR(100) variable that contains the name of the table. It just doesn't work like this.

It looks like you want to run a particular query against many tables with similar structure in your database. Quite often it means that the database schema could be improved. But, if you have to deal with what you have you'll have to use dynamic SQL. This link to MySQL documentation has an example "that demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable", which is exactly what you need.

Inside your loop you need to build a string with the SQL query and use EXECUTE.

SET @s = CONCAT('select count(distinct signature) from ', tableName);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

As far as I understand, the result of the EXECUTE is sent to the caller of the stored procedure as if it was a normal SELECT, so in this example the caller will receive multiple result sets if your database has more than one table where table_name like "%FAULT_20150320%".

Here is a link to another SO question about MySQL dynamic SQL How To have Dynamic SQL in MySQL Stored Procedure with some examples.

It looks like you want something like this. It should sum up counts from several tables in signatureCount variable.

CREATE PROCEDURE CountSignatures()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE signatureCount INT;
    DECLARE tableName CHAR(100);
    DECLARE tableList CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_name LIKE "%FAULT_20150320%";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET signatureCount = 0;
    OPEN tableList;
    tableListLoop: LOOP
        SET done = FALSE;
        FETCH tableList INTO tableName;
        IF done THEN
            LEAVE tableListLoop;
        END IF;

        SET @VarCount = 0;
        SET @VarSQL = CONCAT('SET @VarCount = (SELECT COUNT(DISTINCT signature) FROM ', tableName, ')');

        PREPARE stmt FROM @VarSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET signatureCount = signatureCount + @VarCount;
    END LOOP;
    CLOSE tableList;

    SELECT signatureCount;
END$$

Another variant, if the number of tables that you need to process is not much, is to build dynamically one big SQL statement that includes all tables inside your loop and then EXECUTE it in one go:

SELECT 
(COUNT(DISTINCT signature) FROM Table1) +
(COUNT(DISTINCT signature) FROM Table2) +
...
(COUNT(DISTINCT signature) FROM TableN) AS TotalCount