Here are two ways to return a list of tables in all attached databases in SQLite.

The first method returns all tables and views for all attached databases.

The second method gives you the option of returning both tables and views, or just tables, but only for the primary database.

Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article.

The .tables Command

The easiest way to return a list of tables when using the SQLite command line shell is to use the .tables command.

This command can be used with or without an argument. If you use it without providing an argument, it returns all tables (and views) for all attached databases.

Example:

.tables

Result:

Album          Employee       InvoiceLine    PlaylistTrack
Artist         Genre          MediaType      Track        
Customer       Invoice        Playlist     

In my case, there’s only one attached database (the Chinook sample database), and all of this database’s tables are returned.

As mentioned, you can also provide an argument to this command. Such an argument can be used to limit the tables returned by the command. For example, you can name a specific table, or you can use pattern matching to return only tables that match a given pattern.

Example:

.tables a%

Result:

Album   Artist

In this case only tables that begin with the letter “a” are returned.

One thing to be mindful of is that the .tables command returns both tables and views. If you want to exclude views from your results, you could use pattern matching to exclude views. This will only work if your views use a naming convention that distinguishes them from tables and other objects.

Another way to exclude views from your results is to query the sqlite_schema table directly. Although this table also contains views, you can use SQL to exclude them from your results if need be.

The sqlite_schema Table

Every SQLite database has an sqlite_schema table that defines the schema for the database. You can use this table to return a list of tables in your database.

When you use the .tables command, it’s similar to doing this:

SELECT name FROM sqlite_schema 
WHERE type IN ('table','view') 
AND name NOT LIKE 'sqlite_%'
ORDER BY 1;

However, there is a difference.

The difference is that this method only returns results for the primary database (the .tables command returns results for all attached databases).

Running the above query returns the following result:

Album
Artist
Customer
Employee
Genre
Invoice
InvoiceLine
MediaType
Playlist
PlaylistTrack
Track

That query returns both tables and views (just as the .tables command does).

In my case there aren’t any views, but if you want to exclude views in the results, use this:

SELECT name FROM sqlite_schema 
WHERE type = 'table' 
AND name NOT LIKE 'sqlite_%'
ORDER BY 1;

Result:

Album
Artist
Customer
Employee
Genre
Invoice
InvoiceLine
MediaType
Playlist
PlaylistTrack
Track

The sqlite_schema table can also be accessed by using sqlite_master.

Exclude Views

For the sake of completeness, here’s a quick example that uses a database with a view. This database contains one table (called Products) and one view (called vProducts).

Connect to SQLite/the database:

sqlite3 Store.db

Run the .tables command:

.tables

Result:

Products   vProducts

Query the sqlite_schema table for tables and views:

SELECT name FROM sqlite_schema 
WHERE type IN ('table','view') 
AND name NOT LIKE 'sqlite_%'
ORDER BY 1;

Result:

Products
vProducts

Now query sqlite_schema for tables only:

SELECT name FROM sqlite_schema 
WHERE type = 'table' 
AND name NOT LIKE 'sqlite_%'
ORDER BY 1;

Result:

Products

Temporary Tables

The .table command returns both permanent tables and temporary tables. The sqlite_schema table only contains permanent tables. If you need to return just the temporary tables, you can query sqlite_temp_schema or its synonym sqlite_temp_master.

To return both permanent tables and temporary tables, you can use a query like this:

SELECT name FROM 
   (SELECT * FROM sqlite_schema UNION ALL
    SELECT * FROM sqlite_temp_schema)
WHERE type='table'
ORDER BY name;

Bonus 3rd Option: The table_list Pragma Statement

Since I first wrote this article, SQLite has introduced the table_list pragma statement, which lists out tables and views:

PRAGMA table_list;

See PRAGMA table_list in SQLite for an overview and examples.