schema_auto_increment_columns Sys Schema View

The schema_auto_increment_columns view lists tables with AUTO_INCREMENT columns, showing the current value and the maximum allowed value for the column type.

Sys Schema views are available from MariaDB 10.6.

Description

Information about AUTO_INCREMENT columns, sorted by descending usage ratio and maximum column value. Does not include columns in the mysql, sys, information_schema and performance_schema schemas.

Contains the following columns:

Column	Description
table_schema	Schema name containing the table with the auto_increment attribute.
table_name	Table containing the auto_increment attribute.
column_name	Name of the column containing the auto_increment attribute.
data_type	Data type of the auto_increment column, for example tinyint.
column_type	Data type of the auto_increment column, plus additional information for example tinyint(3) unsigned.
is_signed	1 if the column is signed,0 if not.
is_unsigned	1 if the column is unsigned,0 if it is.
max_value	Maximum possible value for the column, for example 255 for an unsigned tinyint.
auto_increment	Current auto_increment value for the column.
auto_increment_ratio	Ratio of used to maximum value for the auto_increment column.

Example

CREATE OR REPLACE TABLE animals (
    id TINYINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

 INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('fox'),('whale'),('ostrich');

SELECT * FROM sys.schema_auto_increment_columns\G
*************************** 1. row ***************************
        table_schema: test
          table_name: animals
         column_name: id
           data_type: tinyint
         column_type: tinyint(4)
           is_signed: 1
         is_unsigned: 0
           max_value: 127
      auto_increment: 7
auto_increment_ratio: 0.0551

_{This page is licensed: CC BY-SA / Gnu FDL}