onepiece onepiece - 1 year ago 113
MySQL Question

Unknown table 'table_name' in information_schema

I want to

show index
from each table that has
(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
as a table in
. How do I rewrite the query to treat
as a column in

Answer Source

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 
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download