Here are four options for showing all views within an SQLite database.
The sqlite_schema Table
Every SQLite database contains a single sqlite_schema table that stores the schema for that database. The schema for a database is a description of all of the other tables, indexes, triggers, and views that are contained within the database.
We can query this table to return just the views:
SELECT name
FROM sqlite_schema
WHERE type = 'view';Example result:
name -------- v1 vArtists vAlbums vGenres
In my case, I have four views in the database.
The sqlite_master Table
For historical compatibility, the sqlite_schema table can also be referred to as sqlite_master.
So we can change the previous example to the following:
SELECT name
FROM sqlite_master
WHERE type = 'view';Example result:
name -------- v1 vArtists vAlbums vGenres
The .tables Command
We can also use the .tables command to return views.
The .table command queries the sqlite_schema table for all attached databases (not just the primary database).
This command returns both tables and views, so it may not be as useful as the previous methods. However, if you have a consistent naming convention for your views, it could be a quick and easy way to get a list of views in the database.
Example:
.tablesExample result:
Albums Customers OrderItems Products v1 vArtists Artists Genres Orders Vendors vAlbums vGenres
In my case, all views are prefixed with v, and so that makes it easier to determine which ones are views and which are tables.
We can also narrow it down by table/view name by appending a pattern to the .table command. This can be handy if you have a clear and distinct naming convention for your views that separates them from tables.
Example:
.tables 'v%'Result:
Vendors v1 vAlbums vArtists vGenres
In this case, my naming convention helped, but it didn’t exclude all tables (Vendors is a table). Either way, it still narrowed the results down and made it easier to see all views with a quick glance.
The table_list Pragma Statement
Here’s a more recent addition to SQLite. The table_list pragma statement was introduced in SQLite 3.37.0 (released on 2021-11-27). This pragma statement lists out tables and views.
Example:
PRAGMA table_list;Result:
schema name type ncol wr strict --------- ------------------------------ ----- ---- -- ------ main sqlite_schema table 5 0 0 temp sqlite_temp_schema table 5 0 0 Northwind Sales by Category view 4 0 0 Northwind Sales Totals by Amount view 4 0 0 Northwind Products by Category view 5 0 0 Northwind Summary of Sales by Quarter view 3 0 0 Northwind Product Sales for 1997 view 3 0 0 Northwind Order Subtotals view 2 0 0 Northwind Invoices view 26 0 0 Northwind Quarterly Orders view 4 0 0 Northwind Customer and Suppliers by City view 4 0 0 Northwind Alphabetical list of products view 11 0 0 Northwind Order Details Extended view 7 0 0 Northwind Category Sales for 1997 view 2 0 0 Northwind Products Above Average Price view 2 0 0 Northwind Orders Qry view 20 0 0 Northwind Suppliers table 12 0 0 Northwind Summary of Sales by Year view 3 0 0 Northwind Regions table 2 0 0 Northwind Orders table 14 0 0 Northwind sqlite_schema table 5 0 0 Northwind Categories table 4 0 0 Northwind sqlite_sequence table 2 0 0 Northwind Products table 10 0 0 Northwind CustomerDemographics table 2 0 0 Northwind CustomerCustomerDemo table 2 0 0 Northwind Customers table 11 0 0 Northwind Employees table 18 0 0 Northwind Current Product List view 2 0 0 Northwind Territories table 3 0 0 Northwind EmployeeTerritories table 2 0 0 Northwind Shippers table 3 0 0 Northwind Order Details table 5 0 0
We can see which ones are views by looking at the type column.
You can narrow it down to a specific schema, and you can search by table/view name. See PRAGMA table_list in SQLite for an overview and examples of this option.