How do I list all the available views of a particular table in SQLite?

Question

I want to access all the particular views of any particular table in Sqlite . I know I can get the list of all the available tables in the database using sqlite_master

SELECT name from sqlite_master WHERE type='table'; 

And the list of all the available views using

SELECT name from sqlite_master WHERE type ='view';

But I want to find all the available views for a particular table . How do I do that ?

Answer 1

No need to use extension-functions.c; just use the "LIKE" operator:

SELECT name FROM sqlite_master WHERE type = 'view' and sql LIKE "%_tablename_%";

You will get false matches, of course, if you have table names that contain other table names as substrings, or that are substrings of common SQL reserved words (like "here" or "rom"). You can eliminate the latter by the following:

SELECT name FROM sqlite_master WHERE type = 'view' AND sql LIKE "% FROM %tablename% WHERE %";

providing the views you're trying to find conform to the typical model.