0

Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each individual table queried.

  • 1
    Are there any partitioned tables involved ??? – RolandoMySQLDBA Apr 9 at 21:00
  • Generally pull the table names out of information_schema.tables, and construct an run each query. A rather involved stored procedure should do this but overall its better doing in application code. If this is more than a oneoff I'd look to creating a better schema to handle this, e.g. partitioned tables, but maybe something different - depends on other queries/requirements. – danblack Apr 9 at 22:14
0

PROPOSED QUERY

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'MAX(date_create) MaxDateCreated FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    AND table_name LIKE 'ap_form_%'
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

What this does is:

  1. Gathered all tables from the database you ar in that has ap_form_% as a pattern
  2. Create SELECT query for Each ap_form_% table
  3. Concatenate all the Queries into a Single Query
  4. Execute all the Queries as a Single Query

EXAMPLE

This does a table count of all tables in the mysql schema

USE mysql
SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

EXAMPLE EXECUTED

MySQL [mysql]> USE mysql
Database changed
MySQL [mysql]> SET group_concat_max_len = 1024 * 1024 * 10;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
    -> FROM
    -> (
    ->     SELECT
    ->         CONCAT('SELECT ',QUOTE(table_name),' tblname,',
    ->         'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    ->         FROM information_schema.partitions
    ->     WHERE table_schema=DATABASE()
    ->     GROUP BY table_name
    -> ) A;
Query OK, 1 row affected (0.01 sec)

MySQL [mysql]> SELECT @LongQuery\G
*************************** 1. row ***************************
@LongQuery: SELECT 'columns_priv' tblname,COUNT(1) RowCount FROM columns_priv UNION SELECT 'db' tblname,COUNT(1) RowCount FROM db UNION SELECT 'event' tblname,COUNT(1) RowCount FROM event UNION SELECT 'func' tblname,COUNT(1) RowCount FROM func UNION SELECT 'general_log' tblname,COUNT(1) RowCount FROM general_log UNION SELECT 'general_log_backup' tblname,COUNT(1) RowCount FROM general_log_backup UNION SELECT 'help_category' tblname,COUNT(1) RowCount FROM help_category UNION SELECT 'help_keyword' tblname,COUNT(1) RowCount FROM help_keyword UNION SELECT 'help_relation' tblname,COUNT(1) RowCount FROM help_relation UNION SELECT 'help_topic' tblname,COUNT(1) RowCount FROM help_topic UNION SELECT 'host' tblname,COUNT(1) RowCount FROM host UNION SELECT 'innodb_index_stats' tblname,COUNT(1) RowCount FROM innodb_index_stats UNION SELECT 'innodb_table_stats' tblname,COUNT(1) RowCount FROM innodb_table_stats UNION SELECT 'ndb_binlog_index' tblname,COUNT(1) RowCount FROM ndb_binlog_index UNION SELECT 'plugin' tblname,COUNT(1) RowCount FROM plugin UNION SELECT 'proc' tblname,COUNT(1) RowCount FROM proc UNION SELECT 'procs_priv' tblname,COUNT(1) RowCount FROM procs_priv UNION SELECT 'proxies_priv' tblname,COUNT(1) RowCount FROM proxies_priv UNION SELECT 'rds_configuration' tblname,COUNT(1) RowCount FROM rds_configuration UNION SELECT 'rds_global_status_history' tblname,COUNT(1) RowCount FROM rds_global_status_history UNION SELECT 'rds_global_status_history_old' tblname,COUNT(1) RowCount FROM rds_global_status_history_old UNION SELECT 'rds_heartbeat2' tblname,COUNT(1) RowCount FROM rds_heartbeat2 UNION SELECT 'rds_history' tblname,COUNT(1) RowCount FROM rds_history UNION SELECT 'rds_replication_status' tblname,COUNT(1) RowCount FROM rds_replication_status UNION SELECT 'rds_sysinfo' tblname,COUNT(1) RowCount FROM rds_sysinfo UNION SELECT 'servers' tblname,COUNT(1) RowCount FROM servers UNION SELECT 'slave_master_info' tblname,COUNT(1) RowCount FROM slave_master_info UNION SELECT 'slave_relay_log_info' tblname,COUNT(1) RowCount FROM slave_relay_log_info UNION SELECT 'slave_worker_info' tblname,COUNT(1) RowCount FROM slave_worker_info UNION SELECT 'slow_log' tblname,COUNT(1) RowCount FROM slow_log UNION SELECT 'slow_log_backup' tblname,COUNT(1) RowCount FROM slow_log_backup UNION SELECT 'tables_priv' tblname,COUNT(1) RowCount FROM tables_priv UNION SELECT 'time_zone' tblname,COUNT(1) RowCount FROM time_zone UNION SELECT 'time_zone_leap_second' tblname,COUNT(1) RowCount FROM time_zone_leap_second UNION SELECT 'time_zone_name' tblname,COUNT(1) RowCount FROM time_zone_name UNION SELECT 'time_zone_transition' tblname,COUNT(1) RowCount FROM time_zone_transition UNION SELECT 'time_zone_transition_type' tblname,COUNT(1) RowCount FROM time_zone_transition_type UNION SELECT 'user' tblname,COUNT(1) RowCount FROM user
1 row in set (0.00 sec)

MySQL [mysql]> PREPARE qry FROM @LongQuery;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MySQL [mysql]> EXECUTE qry;
+-------------------------------+----------+
| tblname                       | RowCount |
+-------------------------------+----------+
| columns_priv                  |        6 |
| db                            |      207 |
| event                         |        2 |
| func                          |        0 |
| general_log                   |        0 |
| general_log_backup            |        0 |
| help_category                 |       38 |
| help_keyword                  |      453 |
| help_relation                 |      992 |
| help_topic                    |      505 |
| host                          |        0 |
| innodb_index_stats            |    32532 |
| innodb_table_stats            |     3392 |
| ndb_binlog_index              |        0 |
| plugin                        |        0 |
| proc                          |       27 |
| procs_priv                    |        0 |
| proxies_priv                  |        1 |
| rds_configuration             |        1 |
| rds_global_status_history     |        0 |
| rds_global_status_history_old |        0 |
| rds_heartbeat2                |        1 |
| rds_history                   |        1 |
| rds_replication_status        |        1 |
| rds_sysinfo                   |        0 |
| servers                       |        0 |
| slave_master_info             |        0 |
| slave_relay_log_info          |        0 |
| slave_worker_info             |        0 |
| slow_log                      |        0 |
| slow_log_backup               |        0 |
| tables_priv                   |       48 |
| time_zone                     |     1768 |
| time_zone_leap_second         |        0 |
| time_zone_name                |     1768 |
| time_zone_transition          |   118021 |
| time_zone_transition_type     |     8611 |
| user                          |      138 |
+-------------------------------+----------+
38 rows in set (0.00 sec)

MySQL [mysql]> DEALLOCATE PREPARE qry;
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]> 

This should work for you.

GIVE IT A TRY !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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