How to select maximum date_created value from all tables matching certain pattern? 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.
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:


Gathered all tables from the database you ar in that has ap_form_% as a pattern
Create SELECT query for Each ap_form_% table
Concatenate all the Queries into a Single Query
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]&gt; USE mysql
Database changed
MySQL [mysql]&gt; SET group_concat_max_len = 1024 * 1024 * 10;
Query OK, 0 rows affected (0.00 sec)

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

MySQL [mysql]&gt; 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]&gt; PREPARE qry FROM @LongQuery;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MySQL [mysql]&gt; 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]&gt; DEALLOCATE PREPARE qry;
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]&gt; 


This should work for you.

GIVE IT A TRY !!!