Find all auto_increment tables

MySQL Information Schema to the rescue.

I needed to find all auto_increment tables.

SELECT 
    * 
FROM 
    `information_schema`.`COLUMNS`
WHERE 
    `EXTRA` = 'auto_increment' AND 
    `TABLE_SCHEMA` = 'foochoo'

Now I need to make all these tables a larger auto_increment value.

use information_schema;
select table_name 
from tables 
where auto_increment is not null and table_schema=...;

You can then set the auto-increment value as per Change auto increment starting number?
Or, in a single shot (assuming Unix shell):

mysql information_schema -e 
'select concat ("ALTER TABLE ",table_name," AUTO_INCREMENT=1000000") `-- sql` 
from tables 
where auto_increment is not null and table_schema="your-schema";
'|mysql your-schema

Popular Posts