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

I run into this sometimes also and have a very simple trick I use. I just do this (requires Excel or Google Sheets [Warning: I have not tested this process with Google Sheets]):

1. Run SELECT table_name FROM information_schema.tables where table_schema='your_database_name' to get the table list (MySQL).
2. Open a new spreadsheet and copy the table list into column "A" of the sheet starting at row 1.

3. Test your query first in a single table, then when ready, copy the query to column "B" row 1. For MS Excel there is a string limit, so you'll need to concatenate strings as such (I just do line by line):

   ="ALTER TABLE `mydatabasename`.`"&A1&"`
   "&"CHANGE COLUMN `created` `created` DATETIME NOT NULL COMMENT 'Comment here...' ,
   "&"CHANGE COLUMN `updated` `updated` DATETIME NOT NULL COMMENT 'Comment here...' ;"&
   "&"...etc...
   "&"...etc...
   "&"...etc...
   "
   

   The "&" at the beginning is required to capture the new line characters from the previous line end.

4. Select cell B1 and fill the string formula down to match the number of table names.
5. You should not have a list of queries for every table. Select the whole column B and Copy-n-Paste into a new SQL execution window (make sure your target database is set as the default schema to be safe).
6. If you see double quotes " when you paste you'll have to remove them. If in MySQL Workbench just press Ctrl+H and replace all " with nothing (leave second box empty).

This actually works nicely, since the double quote in the formula doesn't conflict in most cases with the SQL text. Also, I concatenated the lines as strings, but you could instead try each line as a separate line on a separate sheet and use a formula to concatenate that way instead. I'm sure creative people can find other similar ways.

Of course this idea works with any database with minor adjustments, so that's a plus. ;)

Answer 2

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