Detect AUTO_INCREMENT capacity in MySQL

auto_increment_capacity.sql

SELECT table_schema,
  table_name,
  data_type,
  ( CASE data_type
    WHEN 'tinyint' THEN 255
    WHEN 'smallint' THEN 65535
    WHEN 'mediumint' THEN 16777215
    WHEN 'int' THEN 4294967295
    WHEN 'bigint' THEN 18446744073709551615
    end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS MAX_VALUE,
  AUTO_INCREMENT,
  AUTO_INCREMENT*100/( CASE data_type
    WHEN 'tinyint' THEN 255
    WHEN 'smallint' THEN 65535
    WHEN 'mediumint' THEN 16777215
    WHEN 'int' THEN 4294967295
    WHEN 'bigint' THEN 18446744073709551615
    end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS percent_capacity
FROM
  information_schema.columns
INNER JOIN
  information_schema.tables USING (table_schema, table_name)
WHERE
  table_schema NOT IN ( 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')
  AND extra = 'auto_increment'