Version 11 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)

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

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

The functionality is similar to the tclODBC command db columns.

   proc GetColumns Table {
           set lRes {}
           set SQL [lindex [db eval "select sql from sqlite_master\
                   where type = \"table\" and tbl_name = \"${Table}\""] 0]
           # > Get outer paranthesis
           if {[regexp {\((.*)\)} $SQL match SQL]} {
                   # Keys might be added at the end: CONSTRAINT ...
                   # > Replace any (.,.,.) by * to get spurious "," away.
                   regsub -all {\([^)]*\)} $SQL * SQL
                   # > now split at , and get all data at the start within \[...\]
                   foreach VarSpec [split $SQL ,] {
                           if {[regexp\
                                   {^[ \t]*\[([^\]]*)\]} $VarSpec match VarName]}\
                           {
                                   lappend lRes $VarName
                           }
                   }
           }
           return $lRes
   }

KBK Far easier would be:

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

See also:

• SQLite
• tdbc