Version 12 of sqlite_master

The SQLite database holds metadata in the table sqlite_master.

Table definition

The table definition is as follows:

• Type : Type is Index or Table.
• name : Index or table name
• tbl_name : Name of the table
• rootpage : rootpage, internal to SQLite
• sql : The SQL statement, which would create this table. Example:

     CREATE TABLE [table2] ([t2c1] VARCHAR, [t2c2] VARCHAR, CONSTRAINT "p_key" PRIMARY KEY ([t2c1], [t2c2]) ON CONFLICT ABORT)

find defined tables

The following procedure returns a list of defined tables. Its functionality is similar to the tclODBC command db tables.

   proc GetTables {{NameStart ""}} {
           return [db eval "select tbl_name from sqlite_master\
                   where (type = \"table\") and (tbl_name like \"${NameStart}%\")"]
   }

find defined columns of a table (from KBK)

The functionality is similar to the tclODBC command db columns.

proc cols {db table} {
    set result {}
    $db eval "PRAGMA TABLE_INFO($table)" row { lappend result $row(name) }
    return $result
}

Harald Oehlmann: Got the following by E-Mail from Kevin Kenny, which guides how to implement the functions below much easier:

 Yup, that's in tdbc::sqlite already.  SQLite gives you some help
 with it: http://www.sqlite.org/pragma.html shows a lot.  Look for
 PRAGMA table_info('myTable') to get the list of columns in a table,
 and PRAGMA index_list('myTable') to get the list of indices.
 (And PRAGMA index_info('myIndex') to get the list of columns
 that determine an index's ordering...) 

See also:

• SQLite
• tdbc