[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)


<<categories>>Enter Category Here