SELECT table_schema, table_name, column_name, column_type, auto_increment,max_int,ROUND(auto_increment/max_int*100,2) AS “USED %” FROM
(SELECT table_schema, table_name, column_name, column_type, auto_increment,
pow(2, case data_type
when ‘tinyint’ then 7
when ‘smallint’ then 15
when ‘mediumint’ then 23
when ‘int’ then 31
when ‘bigint’ then 63
end+(column_type like ‘% unsigned’))-1 as max_int
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema,table_name)
WHERE t.table_schema not in (‘mysql’,’information_schema’,’performance_schema’) and t.table_type = ‘base table’
and c.extra = ‘auto_increment’ AND t.auto_increment IS NOT NULL order by auto_increment desc limit 10) TMP;
Here is my test result

+————–+————+————-+——————+—————-+————+——–+
| table_schema | table_name | column_name | column_type | auto_increment | max_int | USED % |
+————–+————+————-+——————+—————-+————+——–+
| edxapp | andy | id | int(10) unsigned | 4294967295 | 4294967295 | 100.00 |
| examlogin | andy | id | int(11) | 2147483641 | 2147483647 | 100.00 |
+————–+————+————-+——————+—————-+————+——–+
2 rows in set (0.11 sec)

I marked the text “USER_INPUT” between , It disappeared

select tab as ‘Table’, col as ‘Column’, cv as ‘Current Value’, ud as ‘Appox. Usage Period (Days)’, round(usg,2) as ‘Usage %’ from (select concat(table_schema,’.’,table_name) as tab, concat(column_name,’ ‘,column_type,’ ‘,if(locate(‘unsigned’, column_type)=0,’signed’,”)) as col, auto_increment as cv, datediff(now(),create_time) as ud, ((auto_increment/(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)))*100) as usg 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 table_type = ‘base table’ and extra = ‘auto_increment’ order by 5 desc, 3 desc) as get_auto_inc_usage where usg >= limit 10;

USER_INPUT -> In % (Auto Increment Usage)


Sample Output:
+---------------------------------+-------------------------------+---------------+----------------------------+---------+
| Table | Column | Current Value | Appox. Usage Period (Days) | Usage % |
+---------------------------------+-------------------------------+---------------+----------------------------+---------+
| test.tickets | id tinyint(4) signed | 100 | 0 | 78.74 |
| helpdesk.hd_ticket_priority | priority_id tinyint(4) signed | 5 | 406 | 3.94 |
| helpdesk.hd_syslog | log_id int(11) unsigned | 439118 | 198 | 0.01 |