0

I want to show index from each table that has table_schema='foo' (database name).

mysql> show index from table_name from information_schema.tables where table_schema='foo';
ERROR 1109 (42S02): Unknown table 'table_name' in information_schema

From the error, I see that the query treats 'table_name' as a table in information_schema. How do I rewrite the query to treat 'table_name' as a column in information_schema.tables?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
onepiece
  • 3,279
  • 8
  • 44
  • 63
  • I believe this is what you are looking for: http://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table – whitwhoa Oct 26 '16 at 00:24
  • Why would you ever think that `from table_name from` would be valid syntax? It's not even close to anything acceptable. – Ken White Oct 26 '16 at 00:31

1 Answers1

0

You're approaching this wrong, and you're making up syntax that doesn't exist.

I suggest the way you want to get the indexes is by reading the INFORMATION_SCHEMA.STATISTICS table, not the TABLES table.

The following query has the same columns as SHOW INDEXES:

SELECT table_name AS `Table`, Non_unique, index_name AS Key_name,
  Seq_in_index, Column_name, Collation, Cardinality, Sub_part,
  Packed, Nullable, Index_type, Comment, Index_comment 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = 'foo';

You might think there should be an I_S table called "INDEXES" but in fact the system table for index objects is named "STATISTICS". Go figure.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This does give my desired result. But is there a way to programmatically get all the tables instead of typing them all out? Also, I realize your query is the better approach, but is there syntax for what I was originally trying to do? Basically execute a query on each item of a list returned by another query. – onepiece Oct 26 '16 at 00:44
  • No, there is no syntax for `SHOW INDEXES` to have it take the result of another query as the list of tables. You'd have to run the query to get the list of tables, and as a second step, build a dynamic SQL query with a `SHOW INDEXES ...` statement. – Bill Karwin Oct 26 '16 at 01:46