I had this patch for a while where one can get listing of both session and global temporary tables across all sessions. It really helped lot of times to understand the bottlenecks of some of the temporary table issues as MySQL never exposed them in the form of SHOW TABLES.
I also added a new status variable called ‘Created_tmp_heap_to_disk_tables‘, which keeps track of how many memory based temp tables are re-created back to disk based.
The patch is now ported to newer MySQL versions, both 5.0 and 5.1; and it works great on most of the platforms that I tested (Mac, Linux and Windows)
It introduces two new INFORMATION_SCHEMA tables, TEMPORARY_TABLES and GLOBAL_TEMPORARY_TABLES along with supporting regular SHOW syntax
SHOW [SESSION/GLOBAL] TEMPORARY TABLES [FROM db]… |