CODEWITHSUNDEEP
sqliteshell
alecail
alecail

Reputation: 4052

How to find a string in a sqlite3 table?

Is there a pure sqlite request to find a string in a database ?

With a bit of shell, I can extract the schemas from the tables:

for i in *sqlite; 
   do sqlite3 $i ".tables"
   | grep -Eoh "[^ ]+" 
   | while read t; do 
          echo -n "$i:$t  ";
          sqlite3 $i '.schema "'"$t"'"';
   done;
echo;
done

Then I could find which column has the type TEXT, and make a request.

But is there a pure SQL way to do this in a terminal ?

Upvotes: 3

Views: 2206

Answers (1)

CL.
CL.

Reputation: 180070

TLDR; To dump a sqlite db to a file called dump.sql

sqlite3 some.db .dump > dump.sql

SQLite is designed as an embedded database and therefore does have only extremely basic programming capabilities (it has triggers, but no control logic like if or loops). So it is not possible to do anything dynamic in SQL – the result of PRAGMA table_info cannot be directly used in SQL queries.

A very quick and dirty way to search everywhere in a database is to show all contents with .dump and then just grep that. However, this isn't actually quick because searching indexed columns would be faster in SQL.

Upvotes: 3

Related Questions