Unknown table 'table_name' in information_schema

Question

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?

Answer 1

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.