sqlitefull-text-searchfts5

How to fetch names of virtual tables?

Example schema:

CREATE VIRTUAL TABLE posts USING FTS5(title, body);

Select table names:

SELECT name FROM sqlite_master WHERE type='table';

Result: 

posts
posts_data
posts_idx
posts_content
posts_docsize
posts_config

How to fetch result only for virtual tables, without *_data, *_idx, *_content, *_docsize and *_config?

Solution

  • The FTS modules use shadow tables to store the actual data and its indexes.

    But those are 'real' tables, so you can simply use a filter to get only sqlite_master entries for virtual tables:

    SELECT name
FROM sqlite_master
WHERE type = 'table'
  AND sql LIKE 'CREATE VIRTUAL TABLE%';