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

<<categories>> SQLite