Calculate the Size of SQL Tables Using SQLite

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/bin/bash
TMP_DB=$(mktemp)
cat $1 | sqlite3 $TMP_DB
for t in $(sqlite3 $TMP_DB .tables)
do
    #echo -e "\n$t"; sqlite3 $TMP_DB "PRAGMA table_info($t)"
    echo -n "$t: "
    sqlite3 $TMP_DB "PRAGMA table_info($t)" | cut -d'|' -f3 |tr '[:lower:]' '[:upper:]' | \
        sed -e 's/^DEC\(IMAL\)\?.*$/8/' -e 's/.*(\(.\+\)).*/\1/' \
        -e 's/^TEXT$/1/' \
        -e 's/^TIMESTAMP$/8/' -e 's/^DATETIME$/8/' \
        -e 's/^INT\(INTEGER\)\?$/4/' -e 's/^.*BIGINT$/8/' -e 's/^FLOAT$/8/' \
        -e 's/^TINYINT$/1/' -e 's/^SMALLINT$/2/' | \
        paste -s -d+ | bc
done
rm -f $TMP_DB