Ufdjrw

I can get a list of all columns I want to verify the space available.

SELECT 
 TABLE_NAME, COLUMN_NAME, COLUMN_TYPE 
FROM 
 INFORMATION_SCHEMA.COLUMNS 
WHERE 
 COLUMN_TYPE = 'int(11)' AND 
 TABLE_NAME LIKE 'catalog_category_entity%';

Considering that int(11) is up to 2147483648 (not considering unsigned) I would like to calculate how much I am using from this range.

Individually I could check one like this:

select 
 max(value_id)/2147483648 as usage 
from 
 catalog_product_entity_int;

But I would like to do each on a nice way for all the columns found on the first query.

I would like to know if recursive CTE is the right resource in this case and how to do it or if there is a more elegant way of checking it.

I would like to have this nice quick way of checking without any external tools.

I've found this solution for postgres but I was wondering if I really need the function.
postgres: find all integer columns with its current max value in it

I wrote a solution for this task, but I'm hardly the only person to have done something like this.

select concat('`', table_schema, '`.`', table_name, '`.`', column_name, '`') as `column`,
 auto_increment as `current_int`, max_int, round((auto_increment/max_int)*100, 2) as `pct_max`
from (select table_schema, table_name, column_name, 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)
 join information_schema.key_column_usage k using (table_schema,table_name,column_name)
 where t.table_schema in ('test')
 and k.constraint_name = 'PRIMARY'
 and k.ordinal_position = 1
 and t.auto_increment is not null
) as dt;

https://github.com/billkarwin/bk-tools/blob/master/pk-full-ratio.sql

That query is hard-coded for the test schema, so you need to edit it for your own schema.

The short answer to the question of "is my primary key going to overflow?" is to just alter it to BIGINT UNSIGNED now. That will surely last until the collapse of civilization.

In the same git repo, I have another similar script to check all integer columns, not just auto-increment primary keys. But it's not as much of a concern for other columns.