SQLite introspection

aricb 2010-10-01: I'm a SQLite novice and had a hard time finding the following info, so I thought I'd stick it here for my own sake and hopefully others' as well.

Listing the tables (views, indexes, etc.) in a SQLite database

There's a magical read-only table in every SQLite database called SQLITE_MASTER . It has the columns type, name, tbl_name, rootpage, and sql. So, for example, you can get the names of all tables in a given database using:

$dbhandle eval {SELECT name FROM sqlite_master WHERE type='table'}

Listing the columns in a table/view of a SQLite database

To obtain a detailed list of columns in a table or view, to parse the SQL statement that defines the table. It is found in the sql column of the "SQLITE_MASTER" table. To retrieve a simple list of columns without additional detail, use PRAGMA table_info(tableName). For example, to find out about the columns in someTable:

$dbhandle eval {PRAGMA table_info(someTable)}

This returns one row for each column in the table in question. Each row contains the following items:

1. column id
2. column name
3. column type
4. whether column prohibits NULL values (0=NULLs not prohibited, 1=NULLs prohibited)
5. default value for column
6. whether column is part of the table's primary key (0=not part of primary key, 1=part of primary key)

Listing the column names of a table in an SQLite database

$dbhandle eval {SELECT name FROM pragma_table_info(someTable)}

This returns a list of only the column names of the table in question.

Listing the foreign keys of a table in an SQLite database

$dbhandle eval {PRAGMA foreign_key_list(someTable)}

This returns one row for each foreign key that references a column in the argument table. Each row contains 8 items:

1. column id
2. sequence number
3. target table
4. source column
5. target column
6. action on update
7. action on delete
8. match clause

Listing attached databases (by ATTACH statement)

$dbhandle eval {PRAGMA database_list}

Returns list of attached database names, as SQLite sees them, so these names can be used in queries, like:

set firstDatabase [lindex [$dbhandle eval {PRAGMA database_list;}] 0]
$dbhandle eval "SELECT name FROM $firstDatabase.sqlite_master WHERE type='table'"

Listing indexes related to given table

$dbhandle eval {PRAGMA index_list(table-name)}

Returns 3 columns for each index related to the table:

1. 'name' -> name of the index
2. 'seq' -> sequence number (I don't really know what is the meaning of it. please explain it someone.)
3. 'unique' -> 0/1 if index is unique or not

Listing columns indexed by given index

$dbhandle eval {PRAGMA index_info(index-name)}

Returns 3 columns for each column indexed by the index:

1. 'cid' -> I don't know what is it. Maybe 'Column ID'. Please explain it someone.
2. 'name' -> indexed column name
3. 'seqno' -> I don't know what is it. Please explain it someone.

NEM: I believe TDBC can also provide this information.