[sqlite] How do I check in SQLite whether a table exists?

How do I, reliably, check in SQLite, whether a particular user table exists?

I am not asking for unreliable ways like checking if a "select *" on the table returned an error or not (is this even a good idea?).

The reason is like this:

In my program, I need to create and then populate some tables if they do not exist already.

If they do already exist, I need to update some tables.

Should I take some other path instead to signal that the tables in question have already been created - say for example, by creating/putting/setting a certain flag in my program initialization/settings file on disk or something?

Or does my approach make sense?

The following code returns 1 if the table exists or 0 if the table does not exist.

SELECT CASE WHEN tbl_name = "name" THEN 1 ELSE 0 END FROM sqlite_master WHERE tbl_name = "name" AND type = "table"

class CPhoenixDatabase():
    def __init__(self, dbname):
        self.dbname = dbname
        self.conn = sqlite3.connect(dbname)

    def is_table(self, table_name):
        """ This method seems to be working now"""
        query = "SELECT name from sqlite_master WHERE type='table' AND name='{" + table_name + "}';"
        cursor = self.conn.execute(query)
        result = cursor.fetchone()
        if result == None:
            return False
        else:
            return True

Note: This is working now on my Mac with Python 3.7.1

Use

SELECT 1 FROM table LIMIT 1;

to prevent all records from being read.

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

I thought I'd put my 2 cents to this discussion, even if it's rather old one.. This query returns scalar 1 if the table exists and 0 otherwise.

select 
    case when exists 
        (select 1 from sqlite_master WHERE type='table' and name = 'your_table') 
        then 1 
        else 0 
    end as TableExists

See this:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

You could try:

SELECT name FROM sqlite_master WHERE name='table_name'

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;
}

You can write the following query to check the table existance.

SELECT name FROM sqlite_master WHERE name='table_name'

Here 'table_name' is your table name what you created. For example

 CREATE TABLE IF NOT EXISTS country(country_id INTEGER PRIMARY KEY AUTOINCREMENT, country_code TEXT, country_name TEXT)"

and check

  SELECT name FROM sqlite_master WHERE name='country'

Use:

PRAGMA table_info(your_table_name)

If the resulting table is empty then your_table_name doesn't exist.

Documentation:

PRAGMA schema.table_info(table-name);

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

The table named in the table_info pragma can also be a view.

Example output:

cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
1|json|JSON|0||0
2|name|TEXT|0||0

If you are getting a "table already exists" error, make changes in the SQL string as below:

CREATE table IF NOT EXISTS table_name (para1,para2);

This way you can avoid the exceptions.

If you're using fmdb, I think you can just import FMDatabaseAdditions and use the bool function:

[yourfmdbDatabase tableExists:tableName].

Note that to check whether a table exists in the TEMP database, you must use sqlite_temp_master instead of sqlite_master:

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

Here's the function that I used:

Given an SQLDatabase Object = db

public boolean exists(String table) {
    try {
         db.query("SELECT * FROM " + table);
         return true;
    } catch (SQLException e) {
         return false;
    }
}

This is my code for SQLite Cordova:

get_columnNames('LastUpdate', function (data) {
    if (data.length > 0) { // In data you also have columnNames
        console.log("Table full");
    }
    else {
        console.log("Table empty");
    }
});

And the other one:

function get_columnNames(tableName, callback) {
    myDb.transaction(function (transaction) {
        var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'";
        transaction.executeSql(query_exec, [], function (tx, results) {
            var columnNames = [];
            var len = results.rows.length;
            if (len>0){
                var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx
                for (i in columnParts) {
                    if (typeof columnParts[i] === 'string')
                        columnNames.push(columnParts[i].split(" ")[0]);
                };
                callback(columnNames);
            }
            else callback(columnNames);
        });
    });
}

Using a simple SELECT query is - in my opinion - quite reliable. Most of all it can check table existence in many different database types (SQLite / MySQL).

SELECT 1 FROM table;

It makes sense when you can use other reliable mechanism for determining if the query succeeded (for example, you query a database via QSqlQuery in Qt).

c++ function checks db and all attached databases for existance of table and (optionally) column.

bool exists(sqlite3 *db, string tbl, string col="1")
{
    sqlite3_stmt *stmt;
    bool b = sqlite3_prepare_v2(db, ("select "+col+" from "+tbl).c_str(),
    -1, &stmt, 0) == SQLITE_OK;
    sqlite3_finalize(stmt);
    return b;
}

Edit: Recently discovered the sqlite3_table_column_metadata function. Hence

bool exists(sqlite3* db,const char *tbl,const char *col=0)
{return sqlite3_table_column_metadata(db,0,tbl,col,0,0,0,0,0)==SQLITE_OK;}

SQLite table names are case insensitive, but comparison is case sensitive by default. To make this work properly in all cases you need to add COLLATE NOCASE.

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name' COLLATE NOCASE

The most reliable way I have found in C# right now, using the latest sqlite-net-pcl nuget package (1.5.231) which is using SQLite 3, is as follows:

var result = database.GetTableInfo(tableName);
if ((result == null) || (result.Count == 0))
{
    database.CreateTable<T>(CreateFlags.AllImplicit);
}

Use this code:

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

If the returned array count is equal to 1 it means the table exists. Otherwise it does not exist.

Table exists or not in database in swift

func tableExists(_ tableName:String) -> Bool {
        sqlStatement = "SELECT name FROM sqlite_master WHERE type='table' AND name='\(tableName)'"
        if sqlite3_prepare_v2(database, sqlStatement,-1, &compiledStatement, nil) == SQLITE_OK {
            if sqlite3_step(compiledStatement) == SQLITE_ROW {
                return true
            }
            else {
                return false
            }
        }
        else {
            return false
        }
            sqlite3_finalize(compiledStatement)
    }