Show table structure using SQL query in SQLite:
PRAGMA table_info(table_name);
Example:
$ echo "PRAGMA table_info(uptime);" | sqlite3 perfmetrics-jupyterhub.db
0|ts|TIMESTAMP|0||0
1|ts_str|TEXT|0||0
2|uptime_sec|NUMERIC|0||0
3|idletime_sec|NUMERIC|0||0
4|hostname|TEXT|0||0
5|raw_data|TEXT|0||0
In order to view columns with data, run .header on before the above command.
Example:
sqlite> .header on
sqlite> PRAGMA foo.table_info(meminfo);
Error: unknown database foo
sqlite> PRAGMA table_info(meminfo);
cid|name|type|notnull|dflt_value|pk
0|ts|TIMESTAMP|0||0
1|ts_str|TEXT|0||0
2|mem_total_kb|INT|0||0
3|mem_free_kb|INT|0||0
4|mem_available_kb|INT|0||0
5|buffers_kb|INT|0||0
6|cached_kb|INT|0||0
7|swap_cached_kb|INT|0||0
8|active_kb|INT|0||0
9|inactive_kb|INT|0||0
10|swap_total_kb|INT|0||0
11|swap_free_kb|INT|0||0
12|dirty_kb|INT|0||0
13|mapped_kb|INT|0||0
14|hostname|TEXT|0||0
15|raw_data|TEXT|0||0
sqlite>
Show DDL
In order to show the actual SQL queries to create the tables, use .schema:
$ sqlite3 /data1/sqlite/perfmetrics-jupyterhub.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
iostat loadavg meminfo mpstat uptime vmstat
sqlite> .schema meminfo
CREATE TABLE meminfo (
ts TIMESTAMP,
ts_str TEXT,
mem_total_kb INT,
mem_free_kb INT,
mem_available_kb INT,
buffers_kb INT,
cached_kb INT,
swap_cached_kb INT,
active_kb INT,
inactive_kb INT,
swap_total_kb INT,
swap_free_kb INT,
dirty_kb INT,
mapped_kb INT,
hostname TEXT,
raw_data TEXT
);
CREATE INDEX idx_meminfo_ts ON meminfo (ts);
CREATE INDEX idx_meminfo_hostname ON meminfo (hostname);
sqlite>
Alternatively, you can query sqlite_master:
SELECT sql FROM sqlite_master WHERE name='foo';
Example:
sqlite> SELECT sql FROM sqlite_master WHERE name='meminfo';
sql
CREATE TABLE meminfo (
ts TIMESTAMP,
ts_str TEXT,
mem_total_kb INT,
mem_free_kb INT,
mem_available_kb INT,
buffers_kb INT,
cached_kb INT,
swap_cached_kb INT,
active_kb INT,
inactive_kb INT,
swap_total_kb INT,
swap_free_kb INT,
dirty_kb INT,
mapped_kb INT,
hostname TEXT,
raw_data TEXT
)
sqlite>