How to find all the AUTO_INCREMENT table?

Question

I need to find all the tables in a MySQL database schema that have AUTO_INCREMENT columns.

I need to be able to set all these tables to a large AUTO_INCREMENT value so as not to overlap the base data that will be added to (million?).

I know that it is a bad idea, but I do not expect the base data to exceed 1000 items per table.

Answer 1

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