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**
   * [SQLite]
   * [tdbc]
<<categories>> Database