SQLite is a functional little SQL database that is often found used in embedded systems projects (such as one of my favorites, Stratux).
As far as I know, there are three ways to look at a table’s schema (the 3rd is just and extension of the 2nd).
.schema table_name
Running .schema will show you a ‘CREATE’ statement that can be used to build the database table. The issues I have with using this are that the command only shows the original CREATE, not any subsequent changes such as indexes, are worse yet, new fields! Regardless, here is an example:
.schema traffic
CREATE TABLE traffic (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Icao_addr INTEGER, Reg TEXT, Tail TEXT, Emitter_category INTEGER, OnGround INTEGER, Addr_type INTEGER, TargetType INTEGER, SignalLevel REAL, Squawk INTEGER, Position_valid INTEGER, Lat REAL, Lng REAL, Alt INTEGER, GnssDiffFromBaroAlt INTEGER, AltIsGNSS INTEGER, NIC INTEGER, NACp INTEGER, Track INTEGER, Speed INTEGER, Speed_valid INTEGER, Vvel INTEGER, Timestamp STRING, PriorityStatus INTEGER, Age REAL, AgeLastAlt REAL, Last_seen STRING, Last_alt STRING, Last_GnssDiff STRING, Last_GnssDiffAlt INTEGER, Last_speed STRING, Last_source INTEGER, ExtrapolatedPosition INTEGER, Bearing REAL, Distance REAL, timestamp_id INTEGER);
PRAGMA table_info
This is my favored by far. Format can actually be taken and dropped int a Wiki page, or JIRA case or any other similar document system.. but it’s still not the most ideal, as you can see:
PRAGMA table_info(traffic);
0|id|INTEGER|1||1
1|Icao_addr|INTEGER|0||0
2|Reg|TEXT|0||0
3|Tail|TEXT|0||0
4|Emitter_category|INTEGER|0||0
5|OnGround|INTEGER|0||0
6|Addr_type|INTEGER|0||0
7|TargetType|INTEGER|0||0
8|SignalLevel|REAL|0||0
9|Squawk|INTEGER|0||0
10|Position_valid|INTEGER|0||0
11|Lat|REAL|0||0
12|Lng|REAL|0||0
13|Alt|INTEGER|0||0
14|GnssDiffFromBaroAlt|INTEGER|0||0
15|AltIsGNSS|INTEGER|0||0
16|NIC|INTEGER|0||0
17|NACp|INTEGER|0||0
18|Track|INTEGER|0||0
19|Speed|INTEGER|0||0
20|Speed_valid|INTEGER|0||0
21|Vvel|INTEGER|0||0
22|Timestamp|STRING|0||0
23|PriorityStatus|INTEGER|0||0
24|Age|REAL|0||0
25|AgeLastAlt|REAL|0||0
26|Last_seen|STRING|0||0
27|Last_alt|STRING|0||0
28|Last_GnssDiff|STRING|0||0
29|Last_GnssDiffAlt|INTEGER|0||0
30|Last_speed|STRING|0||0
31|Last_source|INTEGER|0||0
32|ExtrapolatedPosition|INTEGER|0||0
33|Bearing|REAL|0||0
34|Distance|REAL|0||0
35|timestamp_id|INTEGER|0||0
Now.. for my favorite modifier:
PRAGMA table_info with headers!
By turning headers ‘on’ you will now get a tabular list of what each of these fields mean. Note that the first operator command does not use a terminating semi-colon.
.headers on
PRAGMA table_info(traffic);
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|1||1
1|Icao_addr|INTEGER|0||0
2|Reg|TEXT|0||0
3|Tail|TEXT|0||0
[... you get the idea ...]
35|timestamp_id|INTEGER|0||0
There you go.. three (OK two) ways to list a table schema in SQLite.