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.
how to
2 Ways to List the Tables in an SQLite Database
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.
How SQLite Count() Works
The SQLite count() function can be used to return the number of rows in a result set.
It can also be used to return the number of times a given column is not NULL in the result set.
It can be used in two ways. If you pass in the asterisk (*) wildcard character, it will return the total number of rows in the group. If you provide the name of a column, it will return the number of times that column is not NULL.
SQLite Date & Time Functions
SQLite supports five functions for working with dates and times. These are:
All of these functions accept a time string as an argument. They also accept other arguments for modifying/formatting the output.
The strftime() function is the most versatile function of the five. In fact, this function can do everything the other four can.
In other words, you could use strftime() for all your date and time formatting requirements. The other four functions are available solely for convenience.
Return a List of Databases in SQLite
When using the SQLite command line interface, you can use the .databases command to return a list of attached databases. More specifically, it returns the names and file names/location of each attached database.
You can also use the PRAGMA database_list statement to return a list of databases attached to the current database connection.
Include Column Names in SQLite Query Results
By default, the SQLite command line interface displays query results using a pipe-separated list, without column headers.
This might be fine if you’re using the results in an application that doesn’t need the column names, but if you’re just a human, it can make it hard to read the data, especially if there are lots of columns.
Fortunately, there’s a quick way to present the column names in your query results.
Actually, there are at least a couple of ways to do this, and I explain them in this article.