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

Use the charindex function in extension-functions.c to search the Sql column in sqlite_master for the name of your table.

extension-functions.c (look at the bottom of this page) is a user-contributed module that provides mathematical and string extension functions for SQL queries, using the loadable extensions mechanism.

Your final query should look something like this (not tested):

SELECT name from sqlite_master 
  WHERE type ='view' AND charindex(Sql, "tableName") > 0;

Answer 2

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.