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.
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'}
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:
$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:
$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'"
$dbhandle eval {PRAGMA index_list(table-name)};
Returns 3 columns for each index related to the table:
$dbhandle eval {PRAGMA index_info(index-name)};
Returns 3 columns for each column indexed by the index:
NEM I believe TDBC can also provide this information.