list all fields in a DB and how often then appear - SQLite 3

Question

In SQLite 3, I'd like to build a piece of SQL (or otherwise - as long as I can execute it with a basic SQLiteStudio install for example) that can read from all the tables in a given db, and list their fields and how many times they appear across all the tables (based on their names).

example:

First table: Person

Fields:

1. PersonID
2. First Name
3. Last Name

Second table: Address

Fields:

1. PersonID

2. Street

After running the code I'd have a third table called, for example, fields_list, like such:

Field: / Count:

PersonID / 2

First Name / 1

Last Name / 1

Street / 1

Any idea? I'm ok with pure SQL but this sort of looping through the tables themselves is way above what I can do I'm afraid...

thanks in advance!

Andre

Answer 1

You can do this with a little bit of scripting. Suppose my database structure is:

CREATE TABLE a (personID text, name text);
CREATE TABLE b (personID text, address text);

the following shell command will aggregate column names by count (replace foo.sqlite with your database file):

sqlite3 foo.sqlite "select name from sqlite_master where type = 'table'" | while read tableName; do sqlite3 foo.sqlite "pragma table_info($tableName)"; done | awk -F\| '{print $2}' | sort | uniq -c

giving the output:

   1 address
   1 name
   2 personID

Here is a step by step break-down of what it is doing:

Print the names of all tables - one per line

sqlite3 foo.sqlite "select name from sqlite_master where type = 'table'"

Output
a
b

Read each table name, and execute PRAGMA table_name() to print out the table schema:

while read tableName; do sqlite3 foo.sqlite "pragma table_info($tableName)"; done

Output
0|personID|text|0||0
1|name|text|0||0
0|personID|text|0||0
1|address|text|0||0

Use | as the delimiter and grab the second column:

awk -F\| '{print $2}'

Output
personID
name
personID
address

Sort the results:

sort

Output
address
name
personID
personID

Merge unique results, and -c displays the count of how many items something appeared.

uniq -c

Output
   1 address
   1 name
   2 personID