Version 7 of 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" (details at [L1 ]). 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

If you feel so inclined, you can parse the SQL statement that defines the table or view; it will be contained in the sql column of the "SQLITE_MASTER" table mentioned above. However, if you don't need that much detail and don't wish to go to that much trouble, you can use PRAGMA table_info(tableName). For example, to find out about the columns in someTable, you can do:

$dbhandle eval {PRAGMA table_info(someTable)}

This returns one row for each column in the table in question. Each row contains six 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 string 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.