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 On Jul 22, 2:10 am, kensystem <[email protected]> wrote: > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
