[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 [http://www.sqlite.org/faq.html#q7]).  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


**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. 

<<categories>> SQLite