SQLite: Check whether Table exists

Sometimes we want to check first whether a certain table exists before we carry out an SQL statement. This may be necessary, for example, before you create a table and want to be sure that there is no table already with this name. Or we would like to write data into a table or read some data from a table and do not yet know whether the table in question already exists. In this situation, too, an examination can make sense to avoid an error message and to be able to create the table if necessary.

In this tutorial we would like to see what options are available in this context with SQLite. The first important thing for this is the table "sqlite_master" which is managed by SQLite itself. In this table there are the names of all existing tables which can also be queried by using it.

Here is an example of how to check the existence of the table "tab":

SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'tab';

If the result of this query is empty, we know that the table does not exist.

Temporary Tables

Important: Temporary tables can not be retrieved from "sqlite_master" because they are listed in "sqlite_temp_master". If we are interested in temporary tables, we can just replace "sqlite_master" with "sqlite_temp_master" in the query above.

SELECT name FROM sqlite_temp_master WHERE type = 'table' AND name = 'tab_temp';

Here we test for the existence of a temporary table called "tab_temp".

Create Table only if it is not existing

For some applications, other solutions are easier to implement. For example, imagine that one of our programs needs some tables in our database. When starting the program, it is not sure whether the program has been started before and if the appropriate tables are already created. Therefore, we would like to check whether a specific table is already existing so that we can only create the new table if not.

CREATE TABLE IF NOT EXISTS 'tab' (...);

For this case, the statement "CREATE TABLE IF NOT EXISTS" can be the simpler way. This statement is equivalent to the "CREATE TABLE"-statement but it is supplemented by "IF NOT EXISTS". That means that the new table is only created if it is not existing yet. This saves you the detour of checking this before.