Version 3 of sqlite

The SQLite data base 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 equivalent 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 equivalent 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
   }

Background:

Version 3 of sqlite_master