How to find all primary keys which don't have an auto-increment

Question

How to find all primary keys in all tables in a database, which don't have an auto-increment identifier to it. We have a large amount of tables and would like to identify all tables which don't have the auto increment identifier on the primary key.

Answer 1

You can extract this information from the information_schema.columns table

select distinct table_name
from information_schema.columns
where table_schema = 'DATABASENAME'
      and table_name not in (select table_name
                             from information_schema.columns
                             where table_schema = 'DATABASENAME'
                                   and column_key = 'PRI'
                                   and data_type = 'int'
                                   and extra = 'auto_increment')

This looks for all tables in one database having an auto_increment column and then returns the remaining tables. This also correctly detects tables with composite keys.