1

How can I get all indexes for all tables on my DB? Something like show index, but such as I could execute for all tables on a given DB and, more important, that I could use as a subquery.

Please notice that I'm not interested on the primary key, but on another index.

Edit: So the solution that I was looking for is (based on aarbor's answer):

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = schema() and index_name <> 'PRIMARY';
mathiasfk
  • 1,278
  • 1
  • 19
  • 38

1 Answers1

6
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

Taken from: This Post

Community
  • 1
  • 1
aarbor
  • 1,398
  • 1
  • 9
  • 24