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:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

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
Sort by:   newest | oldest | most voted

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