MySQL 5.7: Script to find list of Active InnoDB Temp Tables using INNODB_TEMP_TABLE_INFO

MySQL 5.7 introduced INNODB_TEMP_TABLE_INFO table added into INFORMATION_SCHEMA.

Using INNODB_TEMP_TABLE_INFO, you can get A list of all active Temp Table of MySQL InnoDB Instance.
You can get the information of all active user created and system created Temp Tables.

The Database Administrator can use this script for monitoring total Temp Table in MySQL Server.
Unnecessarily, we should not create a TEMP Table because It requires temporary disk + memory storage and degrade the overall performance of MySQL Server.

Once you get the list of tables using INNODB_TEMP_TABLE_INFO, you can get system generated Temp Table Name.

Check existance of INNODB_TEMP_TABLE_INFO:

1	SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';

Create two sample Temporary Table:

1 2	CREATE TEMPORARY TABLE ABC (ID INT, Name VARCHAR(50)); CREATE TEMPORARY TABLE XYZ (ID INT, Name VARCHAR(50));

Get the information of Temp Table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO   *************************** 1. row ***************************             TABLE_ID: 93                 NAME: #sqlcec_9_bec               N_COLS: 5                SPACE: 76 PER_TABLE_TABLESPACE: FALSE        IS_COMPRESSED: FALSE *************************** 2. row ***************************             TABLE_ID: 92                 NAME: #sqlcec_9_beb               N_COLS: 5                SPACE: 76 PER_TABLE_TABLESPACE: FALSE        IS_COMPRESSED: FALSE 2 rows in set (0.01 sec)