onepiece onepiece - 1 month ago 7
MySQL Question

Unknown table 'table_name' in information_schema

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

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.

Comments