How to Check if a Table Exists in SQLite

by

In SQLite, we can query the sqlite_schema table to find out whether a given table exists.

Prior to SQLite version 3.33.0, this table was referred to as sqlite_master (it can still be referred to as such in subsequent releases for backwards compatibility).

Example

Here’s an example to demonstrate:

SELECT EXISTS (
    SELECT 
        name
    FROM 
        sqlite_schema 
    WHERE 
        type='table' AND 
        name='Customers'
    );

Result:

1

In this case, the table exists and 1 is returned.

Here’s an example of what happens when the table doesn’t exist:

SELECT EXISTS (
    SELECT 
        name
    FROM 
        sqlite_schema 
    WHERE 
        type='table' AND 
        name='Inventory'
    );

Result:

0

The sqlite_master Table

The same query can be run using sqlite_master instead of sqlite_schema:

SELECT EXISTS (
    SELECT 
        name
    FROM 
        sqlite_master 
    WHERE 
        type='table' AND 
        name='Customers'
    );

Result:

1

You can also use sqlite_temp_schema or sqlite_temp_master, but these only work for the TEMP database associated with each database connection.