Searching all columns of a table in Datasette#

I came up with this trick today, when I wanted to run a LIKE search against every column in a table.

The trick is to generate a SQL query that does a LIKE search against every column of a table. We can generate that query using another query:

1
select
2
  'select * from "' || :table || '" where ' || group_concat(
3
    '"' || name || '" like ''%'' || :search || ''%''',
4
    ' or '
5
  )
6
from
7
  pragma_table_info(:table)

Here’s what you get when you run that query against the avengers example table from FiveThirtyEight (pretty-printed):

1
select
2
  *
3
from
4
  "avengers/avengers"
5
where
6
  "URL" like '%' || :search || '%'
7
  or "Name/Alias" like '%' || :search || '%'
8
  or "Appearances" like '%' || :search || '%'
9
  or "Current?" like '%' || :search || '%'
10
  or "Gender" like '%' || :search || '%'
11
  or "Probationary Introl" like '%' || :search || '%'
12
  or "Full/Reserve Avengers Intro" like '%' || :search || '%'
13
  or "Year" like '%' || :search || '%'
14
  or "Years since joining" like '%' || :search || '%'
15
  or "Honorary" like '%' || :search || '%'
16
  or "Death1" like '%' || :search || '%'
17
  or "Return1" like '%' || :search || '%'
18
  or "Death2" like '%' || :search || '%'
19
  or "Return2" like '%' || :search || '%'
20
  or "Death3" like '%' || :search || '%'
21
  or "Return3" like '%' || :search || '%'
22
  or "Death4" like '%' || :search || '%'
23
  or "Return4" like '%' || :search || '%'
24
  or "Death5" like '%' || :search || '%'
25
  or "Return5" like '%' || :search || '%'
26
  or "Notes" like '%' || :search || '%'

Here’s an example search using that generated query.

Same trick for the entire database#

Here’s a query that generates a query that searches every column in every table in the database!

1
with tables as (
2
  select
3
    name as table_name
4
  from
5
    sqlite_master
6
  where
7
    type = 'table'
8
),
9
queries as (
10
  select
11
    'select ''' || tables.table_name || ''' as _table, rowid from "' || tables.table_name || '" where ' || group_concat(
12
      '"' || name || '" like ''%'' || :search || ''%''',
13
      ' or '
14
    ) as query
15
  from
16
    pragma_table_info(tables.table_name),
17
    tables
18
  group by
19
    tables.table_name
20
)
21
select
22
  group_concat(query, ' union all ')
23
from
24
  queries

I tried this against the FiveThirtyEight database and the query it produced was way beyond the URL length limit for Cloud Run.

Here’s the result if run against latest.datasette.io/fixtures:

1
select
2
  '123_starts_with_digits' as _table,
3
  rowid
4
from
5
  "123_starts_with_digits"
6
where
7
  "content" like '%' || :search || '%'
8
union all
9
select
10
  'Table With Space In Name' as _table,
11
  rowid
12
from
13
  "Table With Space In Name"
14
where
15
  "pk" like '%' || :search || '%'
16
  or "content" like '%' || :search || '%'
17
union all
18
select
19
  'attraction_characteristic' as _table,
20
  rowid
21
from
22
  "attraction_characteristic"
23
where
24
  "pk" like '%' || :search || '%'
25
  or "name" like '%' || :search || '%'
26
union all
27
select
28
  'binary_data' as _table,
29
  rowid
30
from
31
  "binary_data"
32
where
33
  "data" like '%' || :search || '%'
34
union all
35
select
36
  'complex_foreign_keys' as _table,
37
  rowid
38
from
39
  "complex_foreign_keys"
40
where
41
  "pk" like '%' || :search || '%'
42
  or "f1" like '%' || :search || '%'
43
  or "f2" like '%' || :search || '%'
44
  or "f3" like '%' || :search || '%'
45
union all
46
select
47
  'compound_primary_key' as _table,
48
  rowid
49
from
50
  "compound_primary_key"
51
where
52
  "pk1" like '%' || :search || '%'
53
  or "pk2" like '%' || :search || '%'
54
  or "content" like '%' || :search || '%'
55
union all
56
select
57
  'compound_three_primary_keys' as _table,
58
  rowid
59
from
60
  "compound_three_primary_keys"
61
where
62
  "pk1" like '%' || :search || '%'
63
  or "pk2" like '%' || :search || '%'
64
  or "pk3" like '%' || :search || '%'
65
  or "content" like '%' || :search || '%'
66
union all
67
select
68
  'custom_foreign_key_label' as _table,
69
  rowid
70
from
71
  "custom_foreign_key_label"
72
where
73
  "pk" like '%' || :search || '%'
74
  or "foreign_key_with_custom_label" like '%' || :search || '%'
75
union all
76
select
77
  'facet_cities' as _table,
78
  rowid
79
from
80
  "facet_cities"
81
where
82
  "id" like '%' || :search || '%'
83
  or "name" like '%' || :search || '%'
84
union all
85
select
86
  'facetable' as _table,
87
  rowid
88
from
89
  "facetable"
90
where
91
  "pk" like '%' || :search || '%'
92
  or "created" like '%' || :search || '%'
93
  or "planet_int" like '%' || :search || '%'
94
  or "on_earth" like '%' || :search || '%'
95
  or "state" like '%' || :search || '%'
96
  or "city_id" like '%' || :search || '%'
97
  or "neighborhood" like '%' || :search || '%'
98
  or "tags" like '%' || :search || '%'
99
  or "complex_array" like '%' || :search || '%'
100
  or "distinct_some_null" like '%' || :search || '%'
101
union all
102
select
103
  'foreign_key_references' as _table,
104
  rowid
105
from
106
  "foreign_key_references"
107
where
108
  "pk" like '%' || :search || '%'
109
  or "foreign_key_with_label" like '%' || :search || '%'
110
  or "foreign_key_with_blank_label" like '%' || :search || '%'
111
  or "foreign_key_with_no_label" like '%' || :search || '%'
112
  or "foreign_key_compound_pk1" like '%' || :search || '%'
113
  or "foreign_key_compound_pk2" like '%' || :search || '%'
114
union all
115
select
116
  'infinity' as _table,
117
  rowid
118
from
119
  "infinity"
120
where
121
  "value" like '%' || :search || '%'
122
union all
123
select
124
  'no_primary_key' as _table,
125
  rowid
126
from
127
  "no_primary_key"
128
where
129
  "content" like '%' || :search || '%'
130
  or "a" like '%' || :search || '%'
131
  or "b" like '%' || :search || '%'
132
  or "c" like '%' || :search || '%'
133
union all
134
select
135
  'primary_key_multiple_columns' as _table,
136
  rowid
137
from
138
  "primary_key_multiple_columns"
139
where
140
  "id" like '%' || :search || '%'
141
  or "content" like '%' || :search || '%'
142
  or "content2" like '%' || :search || '%'
143
union all
144
select
145
  'primary_key_multiple_columns_explicit_label' as _table,
146
  rowid
147
from
148
  "primary_key_multiple_columns_explicit_label"
149
where
150
  "id" like '%' || :search || '%'
151
  or "content" like '%' || :search || '%'
152
  or "content2" like '%' || :search || '%'
153
union all
154
select
155
  'roadside_attraction_characteristics' as _table,
156
  rowid
157
from
158
  "roadside_attraction_characteristics"
159
where
160
  "attraction_id" like '%' || :search || '%'
161
  or "characteristic_id" like '%' || :search || '%'
162
union all
163
select
164
  'roadside_attractions' as _table,
165
  rowid
166
from
167
  "roadside_attractions"
168
where
169
  "pk" like '%' || :search || '%'
170
  or "name" like '%' || :search || '%'
171
  or "address" like '%' || :search || '%'
172
  or "latitude" like '%' || :search || '%'
173
  or "longitude" like '%' || :search || '%'
174
union all
175
select
176
  'searchable' as _table,
177
  rowid
178
from
179
  "searchable"
180
where
181
  "pk" like '%' || :search || '%'
182
  or "text1" like '%' || :search || '%'
183
  or "text2" like '%' || :search || '%'
184
  or "name with . and spaces" like '%' || :search || '%'
185
union all
186
select
187
  'searchable_fts' as _table,
188
  rowid
189
from
190
  "searchable_fts"
191
where
192
  "text1" like '%' || :search || '%'
193
  or "text2" like '%' || :search || '%'
194
  or "name with . and spaces" like '%' || :search || '%'
195
union all
196
select
197
  'searchable_fts_docsize' as _table,
198
  rowid
199
from
200
  "searchable_fts_docsize"
201
where
202
  "docid" like '%' || :search || '%'
203
  or "size" like '%' || :search || '%'
204
union all
205
select
206
  'searchable_fts_segdir' as _table,
207
  rowid
208
from
209
  "searchable_fts_segdir"
210
where
211
  "level" like '%' || :search || '%'
212
  or "idx" like '%' || :search || '%'
213
  or "start_block" like '%' || :search || '%'
214
  or "leaves_end_block" like '%' || :search || '%'
215
  or "end_block" like '%' || :search || '%'
216
  or "root" like '%' || :search || '%'
217
union all
218
select
219
  'searchable_fts_segments' as _table,
220
  rowid
221
from
222
  "searchable_fts_segments"
223
where
224
  "blockid" like '%' || :search || '%'
225
  or "block" like '%' || :search || '%'
226
union all
227
select
228
  'searchable_fts_stat' as _table,
229
  rowid
230
from
231
  "searchable_fts_stat"
232
where
233
  "id" like '%' || :search || '%'
234
  or "value" like '%' || :search || '%'
235
union all
236
select
237
  'searchable_tags' as _table,
238
  rowid
239
from
240
  "searchable_tags"
241
where
242
  "searchable_id" like '%' || :search || '%'
243
  or "tag" like '%' || :search || '%'
244
union all
245
select
246
  'select' as _table,
247
  rowid
248
from
249
  "select"
250
where
251
  "group" like '%' || :search || '%'
252
  or "having" like '%' || :search || '%'
253
  or "and" like '%' || :search || '%'
254
  or "json" like '%' || :search || '%'
255
union all
256
select
257
  'simple_primary_key' as _table,
258
  rowid
259
from
260
  "simple_primary_key"
261
where
262
  "id" like '%' || :search || '%'
263
  or "content" like '%' || :search || '%'
264
union all
265
select
266
  'sortable' as _table,
267
  rowid
268
from
269
  "sortable"
270
where
271
  "pk1" like '%' || :search || '%'
272
  or "pk2" like '%' || :search || '%'
273
  or "content" like '%' || :search || '%'
274
  or "sortable" like '%' || :search || '%'
275
  or "sortable_with_nulls" like '%' || :search || '%'
276
  or "sortable_with_nulls_2" like '%' || :search || '%'
277
  or "text" like '%' || :search || '%'
278
union all
279
select
280
  'table/with/slashes.csv' as _table,
281
  rowid
282
from
283
  "table/with/slashes.csv"
284
where
285
  "pk" like '%' || :search || '%'
286
  or "content" like '%' || :search || '%'
287
union all
288
select
289
  'tags' as _table,
290
  rowid
291
from
292
  "tags"
293
where
294
  "tag" like '%' || :search || '%'
295
union all
296
select
297
  'units' as _table,
298
  rowid
299
from
300
  "units"
301
where
302
  "pk" like '%' || :search || '%'
303
  or "distance" like '%' || :search || '%'
304
  or "frequency" like '%' || :search || '%'

It works!