Show indexes?

[kensystem]

Can the SHOW COLUMNS FROM [tablename] be made to show unique or
primary key, etc, indexes?

OR, what is the join needed for:
SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = 'PUBLIC'
AND TABLE_NAME='sometable'
JOIN....?

TIA,
Ken

[bob mcgee]

Hello Ken,

A couple options:
--Gets all indexes
SELECT * FROM information_schema.indexes WHERE table_schema = 'PUBLIC'
AND table_name='tableName';

To select just unique or primary key indexes, add "AND NOT non_unique"
To select just primary key indexes, add "AND primary_key"

Note: rows with the same ID are compound indexes, with columns used in
the order specified by the ordinal column.
This can be cleaned up to just show one row per index, with "columns"
the columns specified for the index:

FROM INFORMATION_SCHEMA.INDEXES
SELECT
table_catalog,table_schema,table_name,non_unique,primary_key,index_type_name,
GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ',') AS
columns
GROUP BY id HAVING table_schema='PUBLIC' AND table_name='table_name'
ORDER BY id;

With some clever SQL and the GROUP CONCAT/GROUP BY you can even get
the original statements used to generate the indexes! Nifty, eh?

Cheers,
Bob McGee

[kensystem]

Thank you, that was just what I was looking for!!

Much obliged,
-Ken