How do I check in SQLite whether a table exists?

I missed that FAQ entry.

Anyway, for future reference, the complete query is:

SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';

Where {table_name} is the name of the table to check.

Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema

• This will return a list of tables with the name specified; that is, the cursor will have a count of 0 (does not exist) or a count of 1 (does exist)

If you're using SQLite version 3.3+ you can easily create a table with:

create table if not exists TableName (col1 typ1, ..., colN typN)

In the same way, you can remove a table only if it exists by using:

drop table if exists TableName

A variation would be to use SELECT COUNT(*) instead of SELECT NAME, i.e.

SELECT count(*) FROM sqlite_master WHERE type='table' AND name='table_name';

This will return 0, if the table doesn't exist, 1 if it does. This is probably useful in your programming since a numerical result is quicker / easier to process. The following illustrates how you would do this in Android using SQLiteDatabase, Cursor, rawQuery with parameters.

boolean tableExists(SQLiteDatabase db, String tableName){    if (tableName == null || db == null || !db.isOpen())    {        return false;    }    Cursor cursor = db.rawQuery(       "SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?",       new String[] {"table", tableName}    );    if (!cursor.moveToFirst())    {        cursor.close();        return false;    }    int count = cursor.getInt(0);    cursor.close();    return count > 0;}