The query below returns all primary keys and unique key constraints on tables in a MySQL database (schema).

Check out also list of unique indexes.

Query

select stat.table_schema as database_name,
       stat.table_name,
       stat.index_name,
       group_concat(stat.column_name
            order by stat.seq_in_index separator ', ') as columns,
       tco.constraint_type
from information_schema.statistics stat
join information_schema.table_constraints tco
     on stat.table_schema = tco.table_schema
     and stat.table_name = tco.table_name
     and stat.index_name = tco.constraint_name
where stat.non_unique = 0
      and stat.table_schema not in ('information_schema', 'sys',
                                    'performance_schema', 'mysql')
group by stat.table_schema,
         stat.table_name,
         stat.index_name,
         tco.constraint_type
order by stat.table_schema,
         stat.table_name;

Columns

• database_name - database (schema) name
• table_name - name of the table
• index_name - name of the index
• columns - index columns separated with ","
• constraint_type
  • PRIMARY KEY - for primary keys
  • UNIQUE - for constraints that enforce uniqueness

Rows

• One row: represents one index/key in the database (schema).
• Scope of rows: all unique indexes and keys
• Ordered by database (schema) and table name

Sample results