How do I get a list of indexed Columns for a given Table

Question

Given a SQLite database, I need to get a list of what columns in a given Table are indexed, and the sort order. I need to do this from code (C#, though that shouldn't matter), so what I really need is a SQL statement, if one exists, that does this.

I know that I can do this:

SELECT sql FROM SQLite_master WHERE type = 'index' AND tbl_name = 'MyTableName'

And then manually parse the resulting SQL, but is there some metadata somewhere that I can just query that would give me something along these lines?

 ------------------------------------
|   name    | column     | direction |
 ------------------------------------
| idx_a     | ColA       | ASC       |
 ------------------------------------
| idx_a_b   | ColB, ColB | DESC      |
 ------------------------------------

Answer 1

Use the PRAGMA index_list(table-name); and PRAGMA index_info(index-name); extensions.

Update: PRAGMA schema.index_xinfo(index-name); will return the sort order for key columns.

Answer 2

Add whatever conditions you want in where clause. Fields are:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

Pastable select...the important field to include for indexes is "sql". Will not list primary keys defined in the create table statment.

select type, name, tbl_name, sql
FROM sqlite_master
WHERE type='index'