The SQLite database holds the database schema 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}%\")"]

PRAGMA functions to query information of the schema (e.g. sqlite_master)

find defined columns of a table (from KBK)

The functionality is similar to the tclODBC command db columns.

TABLE_INFO calls the attached function once for each column with the following array elements set:

  • cid: Column ID: 0,1,2,...
  • name: column name
  • type: type. Example: varchar (16)
  • notnull: 0/1
  • dflt_value: Default value
  • pk: Primary key: 0/1
proc cols {db table} {
    set result {}
    $db eval "PRAGMA TABLE_INFO($table)" row { lappend result $row(name) }
    return $result

find information about keys

Get primary key columns:

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

Get a matrix of keys by enumerating the unique keys:

The primary key is missing in the list, if it is on a column of type integer.

INDEX_LIST returns:

  • seq : 0,...
  • name: Index name
  • unique: 0/1

INDEX_INFO returns:

  • seqno: 0,1,...
  • cid: Column id
  • name: column name
proc keys {db table} {
    set result {}
    $db eval "PRAGMA INDEX_LIST($table)" row {
        if {$row(unique)} {
            set keycols {}
            $db eval "PRAGMA INDEX_INFO($row(name))" detail {
                lappend keycols $detail(name)
            lappend result $keycols
    return $result

See also