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:

Create two sample Temporary Table:

Get the information of Temp Table:

Anvesh Patel

Leave a Reply

2 Comments on "MySQL 5.7: Script to find list of Active InnoDB Temp Tables using INNODB_TEMP_TABLE_INFO"

Notify of
avatar
Sort by:   newest | oldest | most voted
Anku
Guest

why its resulting N_COLS 5, can u pls elaborate? and how can be more clear about table names?

wpDiscuz