You can do it in Tcl (reusing Jose's example tables) like this: package require sqlite3 sqlite3 dbcmd ~/tmp/grbg.db
dbcmd eval "create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c, t0d)" dbcmd eval "insert into table0 (t0a, t0b, t0c, t0d) values ('text in here', 'Shelby 2002', '2 plus 2 equals 4', 'I am going home soon')" dbcmd eval "create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12)" dbcmd eval "insert into table1 (t10,t11,t12) values ('p001', 'Shelby 2002', '1 plus 1 equals 2')" dbcmd eval "create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c, t2d)" dbcmd eval "insert into table2 (t2a, t2b, t2c, t2d) values ('in here', 'going home', '2020-02-11','Once upon a time...')" set searchstr "plus" set SQL "" set u "" set tables [dbcmd eval "select name from sqlite_master where type='table'"] foreach t $tables { dbcmd eval "pragma table_info($t)" { # cid name type notnull dflt_value pk if {[regexp -nocase -- {char|text} $type] || $type == ""} { append SQL "$u select '$t' as tn, '$name' as cn, $name as val from $t \ where $name like '%$searchstr%' " set u "union" } if {[string length $SQL] > 1000000} { error "too many fields" } } } dbcmd eval $SQL { puts [format "Field %-15s on %-15s has the string %-15s: %s" $cn $tn $searchstr $val] } Field t0c on table0 has the string plus : 2 plus 2 equals 4 Field t12 on table1 has the string plus : 1 plus 1 equals 2 John G On Thu, 13 Feb 2020 at 15:24, Jose Isaias Cabrera <jic...@outlook.com> wrote: > > Scott, on Thursday, February 13, 2020 09:01 AM, wrote... > > > > Can I search all tables and columns of SQLite database for a specific > > text string? I'm sure this question has been asked many times, but I'm > > having trouble finding a solid answer. > > My problem: My clients SQLite database has 11 tables and multiple columns > > (some designated and others not) and they want to be able to search the > > entire database for a specific text or phrase. > > What I have done: I've been searching a couple days and found the Full > > Text search on SQLite home based upon using a virtual table, but I don't > > think that will work. It appears that I may be able to search the > > sqlite_master but it seems it may only contain table and column > information > > only minus the data. > > What I'm working in: This is an Android app written in Java using the > > SQLite > > What I hope to do: Find a simple query statement or combination of > > statements that may help to efficiently query for the string across > tables > > and columns before I resort to multiple queries and methods for all 11 > > tables. > > I'm looking for any experienced thoughts or suggestions anyone may have > > encountered resolving this kind of issue if available. I'm not expecting > > anyone to solve it for me -- just some guidance would be helpful. > > This is a very wide open question. It is a lot of work to create the > query. > I actually have to do this for some tables and some fields, but I know > these > tables and these fields. Here are some questions: > > 1. What are you going to do when you find a string match in a table field? > > 2. Do you need to know that table? Do you need to know the field? > > 3. Do you need the whole content of that field if matched? > > There are just too many questions to help, but it is possible if you know > what do you want to do. Here are some ideas: > a. The command prompt has a .table option that will provide all the tables > available on a DB > b. The .schema [tablename] will give you the table's fields > > Imagine these three tables: > create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c, t0d); > insert into table0 (t0a, t0b, t0c, t0d) values ('text in here', 'Shelby > 2002', '2 plus 2 equals 4', 'I am going home soon'); > create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12); > insert into table1 (t10,t11,t12) values ('p001', 'Shelby 2002', '1 plus 1 > equals 2'); > create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c, t2d); > insert into table2 (t2a, t2b, t2c, t2d) values ('in here', 'going home', > '2020-02-11','Once upon a time...'); > > SELECT > 'field t0a on table0 has the string [plus]: ', t0a from table0 > WHERE t0a LIKE '%plus%' > UNION > SELECT > 'field t0b on table0 has the string [plus]: ', t0b from table0 > WHERE t0b LIKE '%plus%' > UNION > SELECT > 'field t0c on table0 has the string [plus]: ', t0c from table0 > WHERE t0c LIKE '%plus%' > UNION > SELECT > 'field t0d on table0 has the string [plus]: ', t0d from table0 > WHERE t0d LIKE '%plus%' > UNION > SELECT > 'field t10 on table1 has the string [plus]: ', t10 from table1 > WHERE t10 LIKE '%plus%' > UNION > SELECT > 'field t11 on table1 has the string [plus]: ', t11 from table1 > WHERE t11 LIKE '%plus%' > UNION > SELECT > 'field t12 on table1 has the string [plus]: ', t12 from table1 > WHERE t12 LIKE '%plus%' > UNION > SELECT > 'field t2a on table2 has the string [plus]: ', t2a from table2 > WHERE t2a LIKE '%plus%' > UNION > SELECT > 'field t2b on table2 has the string [plus]: ', t2a from table2 > WHERE t2a LIKE '%plus%' > UNION > SELECT > 'field t2a on table2 has the string [plus]: ', t2a from table2 > WHERE t2a LIKE '%plus%' > ; > > For just to search on the string 'plus' you will have to do the > above query. > > sqlite> SELECT > ...> 'field t0a on table0 has the string [plus]: ', t0a from table0 > ...> WHERE t0a LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t0b on table0 has the string [plus]: ', t0b from table0 > ...> WHERE t0b LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t0c on table0 has the string [plus]: ', t0c from table0 > ...> WHERE t0c LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t0d on table0 has the string [plus]: ', t0d from table0 > ...> WHERE t0d LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t10 on table1 has the string [plus]: ', t10 from table1 > ...> WHERE t10 LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t11 on table1 has the string [plus]: ', t11 from table1 > ...> WHERE t11 LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t12 on table1 has the string [plus]: ', t12 from table1 > ...> WHERE t12 LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t2a on table2 has the string [plus]: ', t2a from table2 > ...> WHERE t2a LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t2b on table2 has the string [plus]: ', t2a from table2 > ...> WHERE t2a LIKE '%plus%' > ...> UNION > ...> SELECT > ...> 'field t2a on table2 has the string [plus]: ', t2a from table2 > ...> WHERE t2a LIKE '%plus%' > ...> ; > field t0c on table0 has the string [plus]: |2 plus 2 equals 4 > field t12 on table1 has the string [plus]: |1 plus 1 equals 2 > sqlite> > > So, it's possible, and it you use the .tables and .schema, you can use > java to create these very LOOOOOOONG query. :-) Lots of repetitious > strings and selects. This is the beginning. :-) I hope this helps. > > josé > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users