MySQL: Script to find Last AUTO_INCREMENT value for a Table

In this post, I am sharing a script to find last updated AUTO_INCREMENT of a MySQL Table.

We use LAST_INSERT_ID() function to get the last updated AUTO_INCREMENT value for the particular running session. We can use this with any code block and it will return last updated AUTO_INCREMENT value of the last executed statement.

Database Administrator also requires to find last updated AUTO_INCREMENT value for any table and wants to generate a report for that.

Below is a script to find last updated AUTO_INCREMENT value:

Using INFORMATION_SCHEMA.TABLES:

1 2 3 4

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Database_Name' AND TABLE_NAME = 'Table_Name';

Using STATUS command:

1	SHOW TABLE STATUS FROM Database_Name LIKE 'TableName' ;