MySQL Bugs: #34179: Table list for CHECK TABLE & OPTIMIZE TABLE statement must be hard-coded.

Bug #34179	Table list for CHECK TABLE & OPTIMIZE TABLE statement must be hard-coded.
Submitted:	30 Jan 2008 22:00	Modified:	31 Jan 2008 9:50
Reporter:	Arthur Jammz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1.22	OS:	Windows (XP_SP2)
Assigned to:		CPU Architecture:	Any
Tags:	CHECK TABLE, information_schema, optimize table

Description:
Functions like CHECK TABLE and OPTIMIZE TABLE accept a comma-separated list to check or optimize multiple tables. However, the list must be manually typed. 

It would be nice if the comma-separated list could be a SELECT statement, using a table list generated from the INFORMATION_SCHEMA database. This way, a WHERE clause could be used to filter which database objects you want to run the command on. Also, any new tables that are added that meet the selection criteria can also be processed using the same statement.

How to repeat:
Using the "World" database from the MySQL Administrator class:

This hard-coded list will optimize multiple tables in the 'World' database:

OPTIMIZE TABLE city, country, countrylanguage;

This query will generate a comma-separated list of table names in the 'World' database:

SELECT GROUP_CONCAT(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'world';

Returns: city,country,countrylanguage

It would be nice if this worked:

OPTIMIZE TABLE(SELECT GROUP_CONCAT(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'world');

Suggested fix:
Add ability to specify table list using a SELECT statement (most likely from the INFORMATION_SCHEMA database) or a variable.

This can be easily achieved by using Dynamic SQL and a stored procedure.
Examples here:
http://forge.mysql.com/snippets/view.php?id=13