export SQLite empty tables to csv with headers

Question

When I export tables from an SQLite database to csv files with headers, the tables that are empty return an empty csv file.
I would like to obtain a csv file with just the header in that case.

Here is an example.

Create a data base with tblA and tblB where table A has no data.

sqlite3 test.sqlite

CREATE TABLE tblA (
  ID LONG,
  Col01 TEXT
 );

CREATE TABLE tblB (
  ID LONG,
  Col01 TEXT
 );

INSERT INTO tblB (ID, Col01)
VALUES
(1, "AAA"), 
(2, "BBB");

.quit

Export all tables in csv :

# obtain all data tables from database
tables=`sqlite3 test.sqlite "SELECT tbl_name FROM sqlite_master WHERE type='table' and tbl_name not like 'sqlite_%';"`

for i in $tables ; do 
    sqlite3 -header -csv test.sqlite "select * from $i;" > "$i.csv" ;
done

Check the content of these csv files :

[tmp] : head *csv
==> tblA.csv <==

==> tblB.csv <==
ID,Col01
1,AAA
2,BBB

I would like to obtain this instead :

[tmp] : head *csv
==> tblA.csv <==
ID,Col01

==> tblB.csv <==
ID,Col01
1,AAA
2,BBB

Answer 1

One option is to utilize pragmatable_info to get the column names, and then just append the rows' content:

for i in $tables ; do
    sqlite3 test.sqlite "pragma table_info($i)" | cut -d '|' -f 2 | paste -s -d, > "$i.csv"
    sqlite3 -csv test.sqlite "select * from $i;" >> "$i.csv"
done

Result:

$ cat tblA.csv
ID,Col01
$ cat tblB.csv
ID,Col01
1,AAA
2,BBB