Querying table details in SQLite?

Sqlite_master table explained

Let’s be honest… fetching informations about tables is not the task you will come across every day. Even though it is not so usual, there are still cases when knowing if table exists is crucial. One of the cases could be testing your database schema. Testing database migrations? Sounds helpful to me as well.

But how do you do this? There is a simple answer — Query SQLITE_MASTER table with SELECT statement, like:

SELECT name FROM sqlite_master WHERE type='table';

Hold on! Where did the sqlite_master table came from?

Sqlite_master table is created for each database automatically. It describes the schema of each table with some additional informations. Additionally, database can contain SQLITE_TEMP_MASTER which holds information about views, their indices and triggers.

Type column can be either a table or index. Depending of the type, name and tbl_name can take different values.

Name and tbl_name are the most interesting columns. Name is simply the name of table or index, where tbl_name, when type is index, is the table to which the index belongs. For type table tbl_name is NULL.

Rootpage is the page number of the root b-tree page. For rows that define views, triggers, and virtual tables, the rootpage column is zero or NULL.

Sql is just a CREATE statement for table or index. For automatically created indices (to implement PRIMARY KEY or UNIQUE constraints) sql is set to NULL.

Now, when we know what informations we can get, let’s see how easily can we find out whether the table exists in database.

As described above, name column keeps the name of the table. The Above example assumes that what we are interested in are only tables and not tables which belong to indices (which would be kept in tbl_name column).

One point to consider is whether to query temporary tables as well. In this case, the following version would be more appropriate.

Let’s take a look at the real live scenario. The task is to create tables for each supported language. Test cases like below show how to use presented method to check whether all expected tables are created.

If you are into topics like that one, check my other posts about automated Android and SQLite. The short, but still growing, list of articles on that topic looks like the following:

• Simple trick to make your SQLite Database tests faster on Android shows how to prepare database object for fast testing.
• Android Database Schema Testing shows how to use PRAGMA Statements to assert database state.