sql - How can I select sqlite "check ... in [list]" options in SQLite?

Question

Pretty much everything is in the title. I'm looking for an SQLite equivalent of the following SQL command :

show columns from users where field = "category";

Given that the show statement doesn't exist in SQLite. Thanks in advance

question from:https://stackoverflow.com/questions/65881466/how-can-i-select-sqlite-check-in-list-options-in-sqlite

Answer 1

You can use meta data held in SQLite with query:

select
  sm.name as table_name,
  pti.name as column_name
from
  sqlite_master sm
join
  pragma_table_info(sm.name) as pti
where
  pti.name like '%category%';

It returns (as can be seen) tables with column names. Then, you can filter output by column name, I used for example condition:

where pti.name like '%category%'

which returns only those column names including category.