View the auto_increment value for a table in MySQL without using SHOW TABLE?

To view the auto_increment value for a table, you can use INFORMATION_SCHEMA.TABLES.

Let us first create a table −

mysql> create table viewtheauto_incrementValueForATableDemo
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(20)
   -> );
Query OK, 0 rows affected (0.84 sec)

Following is the query to insert some records in the table using insert command −

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName)
values('Ramit');
Query OK, 1 row affected (0.23 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName) values('Bob');
Query OK, 1 row affected (0.14 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName) values('Chris');
Query OK, 1 row affected (0.15 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName)
values('Robert');
Query OK, 1 row affected (0.12 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName) values('John');
Query OK, 1 row affected (0.46 sec)

Following is the query to display all records from the table using a select statement −

mysql> select * from viewtheauto_incrementValueForATableDemo;

This will produce the following output −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Ramit       |
| 2         | Bob         |
| 3         | Chris       |
| 4         | Robert      |
| 5         | John        |
+-----------+-------------+
5 rows in set (0.00 sec)

Following is the query to view the auto_increment value for a table −

mysql> SELECT `AUTO_INCREMENT` AS 'NextAutoIncrementValue'
   -> FROM `information_schema`.`TABLES`
   -> WHERE `TABLE_SCHEMA` = 'test'
   -> AND `TABLE_NAME` = 'viewtheauto_incrementValueForATableDemo';

This will produce the following output −

+------------------------+
| NextAutoIncrementValue |
+------------------------+
| 6                      |
+------------------------+
1 row in set (0.00 sec)

Anvi Jain

Published on 15-Apr-2019 12:11:05

• Related Questions & Answers
• How do I view the auto_increment value for a table in MySQL?
• How do I get the current AUTO_INCREMENT value for a table in MySQL?
• Can we insert records in a MySQL table without auto_increment values?
• Create table query with manual AUTO_INCREMENT start value in MySQL?
• Select the maximum for each value in a MySQL table?
• MySQL INSERT INTO SELECT into a table with AUTO_INCREMENT
• Set AUTO_INCREMENT in a table while creating it in MySQL?
• Fix Drop table view #1051 unknown table error in MySQL
• Setting similar value for a column in a MySQL table?
• Show constraints on table command in MySQL?
• Set value only for NULL values in a MySQL table
• How to create a table from view in MySQL?
• Show a MySQL user-defined variables values in the result table?
• MySQL show tables sort by table name?
• Set auto increment initial value for MySQL table using ALTER command