The power of dynamic queries

Dynamic queries (a.k.a. prepared statements) have been rather neglected in the list of new features available in MySQL 5.
However, they are simply priceless when you try to achieve something unusual.

Let's start with a common problem. You would like to get the list of your tables with the number of records for each one.
In the old times before MySQL 5, you would do SHOW TABLE STATUS. But that output is large, difficult to display, and it has a serious fault, since for InnoDB tables the number of rows is a calculated guess, rather than a precise count.
What you'd really want is this (using the world sample database)

SELECT 'City' AS `table`, COUNT(*) as records FROM City
UNION
SELECT 'Country', COUNT(*) FROM Country
UNION
SELECT 'CountryLanguage', COUNT(*) FROM CountryLanguage

+-----------------+---------+
| table           | records |
+-----------------+---------+
| City            |    4079 |
| Country         |     239 |
| CountryLanguage |     984 |
+-----------------+---------+
3 rows in set (0.00 sec)

But such a query is too much to write for three tables. Simply unbearable when we have 10 or more.
We can do better than that.

delimiter //

create database if not exists glob //

use glob //

drop function if exists get_create_table_list //

create function get_create_table_list ( db_name varchar(30) )
returns varchar(8000)
deterministic
begin
 declare query varchar(8000);
 set group_concat_max_len=8000;
 set query = (select group_concat(concat("SELECT '",
 table_name, "' AS `table`, COUNT(*) as records
 FROM ", table_schema,".", table_name) separator ' UNION ' )
 from information_schema.tables where table_schema = db_name  );
 return query;
end//

drop procedure if exists show_table_list //

create procedure show_table_list  ( db_name varchar(30) )
deterministic
begin
 set @query = get_create_table_list(db_name);
 prepare table_list from @query;
 execute table_list;
end //

delimiter ;

The first routine (get_create_table_list) will return a query like the one I made manually. The second routine is a procedure (show_table_list) that will get that query and execute it.

select glob.get_create_table_list('world') \G
*************************** 1. row ***************************
get_create_table_list('world'): SELECT 'City' AS `table`, COUNT(*) as records
 FROM world.City UNION SELECT 'Country' AS `table`, COUNT(*) as records
 FROM world.Country UNION SELECT 'CountryLanguage' AS `table`, COUNT(*) as records
 FROM world.CountryLanguage

call glob.show_table_list('world');
+-----------------+---------+
| table           | records |
+-----------------+---------+
| City            |    4079 |
| Country         |     239 |
| CountryLanguage |     984 |
+-----------------+---------+

call glob.show_table_list('mysql');
+---------------------------+---------+
| table                     | records |
+---------------------------+---------+
| columns_priv              |       0 |
| db                        |       3 |
| func                      |       0 |
| help_category             |      29 |
| help_keyword              |     325 |
| help_relation             |     548 |
| help_topic                |     405 |
| host                      |       0 |
| proc                      |      17 |
| procs_priv                |       0 |
| tables_priv               |       2 |
| time_zone                 |       0 |
| time_zone_leap_second     |       0 |
| time_zone_name            |       0 |
| time_zone_transition      |       0 |
| time_zone_transition_type |       0 |
| user                      |       5 |
| user_info                 |       0 |
+---------------------------+---------+

Isn't it a beauty?