-2

I am trying to get data like count or distinct from tables matching criteria in information_schema

SELECT 
    count(A.searchstring) 
FROM
    (SELECT 
        table_name 
     FROM 
        information_schema.tables 
     WHERE 
        table_name LIKE 'wozsearch_200716_%') A;

There are 100's tables which have same structure but with different tables names, and I want to get the distinct values and row count of those tables.

A.searchstring is a column name in each table matching with table_name on the condition LIKE 'wozsearch_200716_%'.

From multiple table I can query total rows using:

SELECT sum(TABLE_ROWS)
FROM INFORMATION_SCHEMA.tables 
WHERE table_name LIKE 'wozsearch_200716_%'

But I want to find out:

SELECT distinct searchstring FROM  table_name LIKE 'wozsearch_200716_%'

Error I receive:

Error Code: 1054. Unknown column 'A.searchstring' in 'field list'

I have also tried using stored procedure

CREATE PROCEDURE `getdistinctrEs`(pattern char(50))
BEGIN
 DECLARE vtable_name TEXT;
        DECLARE getTempRecords CURSOR FOR SELECT a.`table_name` FROM INFORMATION_SCHEMA.tables a WHERE a.`table_name` LIKE pattern;
    open getTempRecords;
    c1:LOOP 
    FETCH getTempRecords INTO vtable_name;
  
    set @query=concat("SELECT distinct searchstring FROM ",vtable_name);
    prepare st from @query;
   execute st;
    END LOOP c1;
       
    close getTempRecords;
END

getting result in individual hundred of tabs and mysql stucks and i have to restart workbench

Please help me to solve my issue.

4
  • 1
    There isn't neither an A table nor an A alias in your query. And any column in INFORMATION_SCHEMA.TABLES is named searchstring. Maybe you copy and paste this piece of code?
    – McNets
    Jul 20, 2020 at 13:19
  • 2
    The term you're looking for is "dynamic SQL".
    – mustaccio
    Jul 20, 2020 at 14:05
  • Does this answer your question? count null values of table from information_schema tables
    – mustaccio
    Jul 20, 2020 at 15:05
  • i have updated my answer
    – nbk
    Sep 2, 2020 at 15:07

2 Answers 2

1

The stored procedure will produce for every table a separate result set.

You could INSERT The result of the SELECT into a temporary table, so that you have only one table as result:

DELIMITER // 
CREATE PROCEDURE `getdistinctrEs`(IN pattern varchar(50), IN searchstring varchar(50))
BEGIN
 DECLARE vtable_name TEXT;
        DECLARE getTempRecords CURSOR FOR SELECT a.`table_name` FROM INFORMATION_SCHEMA.tables a WHERE a.`table_name` LIKE pattern;
    open getTempRecords;
    c1:LOOP 
        FETCH getTempRecords INTO vtable_name;
  
        SET @query=concat("SELECT distinct ",searchstring," FROM ",vtable_name);
        PREPARE st from @query;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    END LOOP c1;
       
    close getTempRecords;
END//
DELIMITER ;
0
0

Write a stored procedure to build the SQL and insert the table name into that string. Then "execute" it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.