Version 3 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 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

NEM I believe TDBC can also provide this information.