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:

select
  'select * from "' || :table || '" where ' || group_concat(
    '"' || name || '" like ''%'' || :search || ''%''',
    ' or '
  )
from
  pragma_table_info(:table)

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

select
  *
from
  "avengers/avengers"
where
  "URL" like '%' || :search || '%'
  or "Name/Alias" like '%' || :search || '%'
  or "Appearances" like '%' || :search || '%'
  or "Current?" like '%' || :search || '%'
  or "Gender" like '%' || :search || '%'
  or "Probationary Introl" like '%' || :search || '%'
  or "Full/Reserve Avengers Intro" like '%' || :search || '%'
  or "Year" like '%' || :search || '%'
  or "Years since joining" like '%' || :search || '%'
  or "Honorary" like '%' || :search || '%'
  or "Death1" like '%' || :search || '%'
  or "Return1" like '%' || :search || '%'
  or "Death2" like '%' || :search || '%'
  or "Return2" like '%' || :search || '%'
  or "Death3" like '%' || :search || '%'
  or "Return3" like '%' || :search || '%'
  or "Death4" like '%' || :search || '%'
  or "Return4" like '%' || :search || '%'
  or "Death5" like '%' || :search || '%'
  or "Return5" like '%' || :search || '%'
  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!

with tables as (
  select
    name as table_name
  from
    sqlite_master
  where
    type = 'table'
),
queries as (
  select
    'select ''' || tables.table_name || ''' as _table, rowid from "' || tables.table_name || '" where ' || group_concat(
      '"' || name || '" like ''%'' || :search || ''%''',
      ' or '
    ) as query
  from
    pragma_table_info(tables.table_name),
    tables
  group by
    tables.table_name
)
select
  group_concat(query, ' union all ')
from
  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:

select
  '123_starts_with_digits' as _table,
  rowid
from
  "123_starts_with_digits"
where
  "content" like '%' || :search || '%'
union all
select
  'Table With Space In Name' as _table,
  rowid
from
  "Table With Space In Name"
where
  "pk" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
union all
select
  'attraction_characteristic' as _table,
  rowid
from
  "attraction_characteristic"
where
  "pk" like '%' || :search || '%'
  or "name" like '%' || :search || '%'
union all
select
  'binary_data' as _table,
  rowid
from
  "binary_data"
where
  "data" like '%' || :search || '%'
union all
select
  'complex_foreign_keys' as _table,
  rowid
from
  "complex_foreign_keys"
where
  "pk" like '%' || :search || '%'
  or "f1" like '%' || :search || '%'
  or "f2" like '%' || :search || '%'
  or "f3" like '%' || :search || '%'
union all
select
  'compound_primary_key' as _table,
  rowid
from
  "compound_primary_key"
where
  "pk1" like '%' || :search || '%'
  or "pk2" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
union all
select
  'compound_three_primary_keys' as _table,
  rowid
from
  "compound_three_primary_keys"
where
  "pk1" like '%' || :search || '%'
  or "pk2" like '%' || :search || '%'
  or "pk3" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
union all
select
  'custom_foreign_key_label' as _table,
  rowid
from
  "custom_foreign_key_label"
where
  "pk" like '%' || :search || '%'
  or "foreign_key_with_custom_label" like '%' || :search || '%'
union all
select
  'facet_cities' as _table,
  rowid
from
  "facet_cities"
where
  "id" like '%' || :search || '%'
  or "name" like '%' || :search || '%'
union all
select
  'facetable' as _table,
  rowid
from
  "facetable"
where
  "pk" like '%' || :search || '%'
  or "created" like '%' || :search || '%'
  or "planet_int" like '%' || :search || '%'
  or "on_earth" like '%' || :search || '%'
  or "state" like '%' || :search || '%'
  or "city_id" like '%' || :search || '%'
  or "neighborhood" like '%' || :search || '%'
  or "tags" like '%' || :search || '%'
  or "complex_array" like '%' || :search || '%'
  or "distinct_some_null" like '%' || :search || '%'
union all
select
  'foreign_key_references' as _table,
  rowid
from
  "foreign_key_references"
where
  "pk" like '%' || :search || '%'
  or "foreign_key_with_label" like '%' || :search || '%'
  or "foreign_key_with_blank_label" like '%' || :search || '%'
  or "foreign_key_with_no_label" like '%' || :search || '%'
  or "foreign_key_compound_pk1" like '%' || :search || '%'
  or "foreign_key_compound_pk2" like '%' || :search || '%'
union all
select
  'infinity' as _table,
  rowid
from
  "infinity"
where
  "value" like '%' || :search || '%'
union all
select
  'no_primary_key' as _table,
  rowid
from
  "no_primary_key"
where
  "content" like '%' || :search || '%'
  or "a" like '%' || :search || '%'
  or "b" like '%' || :search || '%'
  or "c" like '%' || :search || '%'
union all
select
  'primary_key_multiple_columns' as _table,
  rowid
from
  "primary_key_multiple_columns"
where
  "id" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
  or "content2" like '%' || :search || '%'
union all
select
  'primary_key_multiple_columns_explicit_label' as _table,
  rowid
from
  "primary_key_multiple_columns_explicit_label"
where
  "id" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
  or "content2" like '%' || :search || '%'
union all
select
  'roadside_attraction_characteristics' as _table,
  rowid
from
  "roadside_attraction_characteristics"
where
  "attraction_id" like '%' || :search || '%'
  or "characteristic_id" like '%' || :search || '%'
union all
select
  'roadside_attractions' as _table,
  rowid
from
  "roadside_attractions"
where
  "pk" like '%' || :search || '%'
  or "name" like '%' || :search || '%'
  or "address" like '%' || :search || '%'
  or "latitude" like '%' || :search || '%'
  or "longitude" like '%' || :search || '%'
union all
select
  'searchable' as _table,
  rowid
from
  "searchable"
where
  "pk" like '%' || :search || '%'
  or "text1" like '%' || :search || '%'
  or "text2" like '%' || :search || '%'
  or "name with . and spaces" like '%' || :search || '%'
union all
select
  'searchable_fts' as _table,
  rowid
from
  "searchable_fts"
where
  "text1" like '%' || :search || '%'
  or "text2" like '%' || :search || '%'
  or "name with . and spaces" like '%' || :search || '%'
union all
select
  'searchable_fts_docsize' as _table,
  rowid
from
  "searchable_fts_docsize"
where
  "docid" like '%' || :search || '%'
  or "size" like '%' || :search || '%'
union all
select
  'searchable_fts_segdir' as _table,
  rowid
from
  "searchable_fts_segdir"
where
  "level" like '%' || :search || '%'
  or "idx" like '%' || :search || '%'
  or "start_block" like '%' || :search || '%'
  or "leaves_end_block" like '%' || :search || '%'
  or "end_block" like '%' || :search || '%'
  or "root" like '%' || :search || '%'
union all
select
  'searchable_fts_segments' as _table,
  rowid
from
  "searchable_fts_segments"
where
  "blockid" like '%' || :search || '%'
  or "block" like '%' || :search || '%'
union all
select
  'searchable_fts_stat' as _table,
  rowid
from
  "searchable_fts_stat"
where
  "id" like '%' || :search || '%'
  or "value" like '%' || :search || '%'
union all
select
  'searchable_tags' as _table,
  rowid
from
  "searchable_tags"
where
  "searchable_id" like '%' || :search || '%'
  or "tag" like '%' || :search || '%'
union all
select
  'select' as _table,
  rowid
from
  "select"
where
  "group" like '%' || :search || '%'
  or "having" like '%' || :search || '%'
  or "and" like '%' || :search || '%'
  or "json" like '%' || :search || '%'
union all
select
  'simple_primary_key' as _table,
  rowid
from
  "simple_primary_key"
where
  "id" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
union all
select
  'sortable' as _table,
  rowid
from
  "sortable"
where
  "pk1" like '%' || :search || '%'
  or "pk2" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
  or "sortable" like '%' || :search || '%'
  or "sortable_with_nulls" like '%' || :search || '%'
  or "sortable_with_nulls_2" like '%' || :search || '%'
  or "text" like '%' || :search || '%'
union all
select
  'table/with/slashes.csv' as _table,
  rowid
from
  "table/with/slashes.csv"
where
  "pk" like '%' || :search || '%'
  or "content" like '%' || :search || '%'
union all
select
  'tags' as _table,
  rowid
from
  "tags"
where
  "tag" like '%' || :search || '%'
union all
select
  'units' as _table,
  rowid
from
  "units"
where
  "pk" like '%' || :search || '%'
  or "distance" like '%' || :search || '%'
  or "frequency" like '%' || :search || '%'

It works!

Created 2021-08-23T11:48:22-07:00, updated 2021-08-23T12:05:47-07:00 · History · Edit