As with most things in SQLite, there’s more than one way to get a list of temporary tables in a database.

Here I present two ways to return temporary tables in SQLite.

The .tables Command

If you’re familiar with the SQLite command line shell, then you’ll probably know about the .tables dot command. This command lists out all tables matching a given pattern (or simply all tables, if no pattern is given). This includes temporary tables.

Example:

CREATE TEMP TABLE TempProducts (id, name, price);
.tables

Result:

Products           temp.TempProducts  vProducts 

In this case, I have one permanent table (Products), one view (vProducts), and one temporary table (temp.TempProducts).

So you can see that both temporary and permanent tables are returned. If your temporary tables use a fixed naming convention, such as a prefix only reserved for temporary tables, you can use pattern matching to retrieve only temporary tables.

Example:

.tables temp%

Result:

temp.TempProducts

However, naming conventions are only good as long as everyone follows them.

If you don’t want permanent tables returned in your results, you’re probably better off querying the sqlite_temp_master table (below).

The sqlite_temp_master Table

SQLite has a sqlite_temp_master table that contains temporary tables and their indices and triggers. This table is only visible to the application that created the temporary table.

You can query this table like this:

SELECT name FROM sqlite_temp_master;

Result:

TempProducts

In this example I only return the table name, but feel free to use an asterisk (*) to return all columns.

As mentioned, this table doesn’t include permanent tables. If you need to return both temporary and permanent tables, and you can’t use .temp, you can include the sqlite_master table in your query.

Example:

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

Result:

Products
TempProducts

If you want to include views, do this:

SELECT name FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type in ('table', 'view')
ORDER BY name;

Result:

Products
TempProducts
vProducts