mpopp.net blog – Markus Popp

The information_schema tables offer all kinds of useful metadata. Also all information about indexes are included in them, although they are split up among several tables.

I’ve built up a query that shows all the information about indexes, here it is:

SELECT a.table_schema, a.table_name, a.constraint_name, 
       a.constraint_type,
       convert(group_concat(DISTINCT b.column_name
       ORDER BY b.ordinal_position SEPARATOR ', '), char) 
       as column_list,
       b.referenced_table_name, b.referenced_column_name
FROM information_schema.table_constraints a
INNER JOIN information_schema.key_column_usage b
ON a.constraint_name = b.constraint_name AND
   a.table_schema = b.table_schema AND
   a.table_name = b.table_name
GROUP BY a.table_schema, a.table_name, a.constraint_name, 
         a.constraint_type, b.referenced_table_name, 
         b.referenced_column_name
UNION
SELECT table_schema, table_name, index_name as constraint_name,
       if(index_type='FULLTEXT', 'FULLTEXT', 'NON UNIQUE') 
       as constraint_type,
       convert(group_concat(column_name 
       ORDER BY seq_in_index separator ', '), char) as column_list,
       null as referenced_table_name, null as referenced_column_name
FROM information_schema.statistics
WHERE non_unique = 1
GROUP BY table_schema, table_name, constraint_name, constraint_type,
         referenced_table_name, referenced_column_name
ORDER BY table_schema, table_name, constraint_name

Actually, at the moment I need this piece of information rather for PostgreSQL than for MySQL (for automatically renaming the index names according to coding standards), but I would have hated to do this without knowing how it works in MySQL. My PostgreSQL equivalent still misses the information for non unique indexes – the rest already works great. So here’s the PostgreSQL equivalent up to what I already have:

SELECT a.table_catalog, a.table_schema, a.table_name, 
       a.constraint_name, a.constraint_type, 
       array_to_string(
         array(
           SELECT column_name::varchar
           FROM information_schema.key_column_usage
           WHERE constraint_name = a.constraint_name
           ORDER BY ordinal_position
           ),
         ', '
         ) as column_list,
       c.table_name, c.column_name
FROM information_schema.table_constraints a 
INNER JOIN information_schema.key_column_usage b
ON a.constraint_name = b.constraint_name
LEFT JOIN information_schema.constraint_column_usage c 
ON a.constraint_name = c.constraint_name AND 
   a.constraint_type = 'FOREIGN KEY'
GROUP BY a.table_catalog, a.table_schema, a.table_name, 
         a.constraint_name, a.constraint_type, 
         c.table_name, c.column_name
ORDER BY a.table_catalog, a.table_schema, a.table_name, 
         a.constraint_name

Unfortunately, the statistics table doesn’t exist in PostgreSQL’s information_schema (is it an extension of MySQL or missing in PostgreSQL?), so I have to find another source for the non-unique index data. I’ll add the final query as comment to this article as soon as I’ve got it.