SQLite3 C interface: how to determine existence of a table

Question

I'm using SQLite3 C interface from C++ and need to be able to determine existence of a given table. Is there a means for this without using SQL directly? Example:

bool exists = sqlite3_table_exists("myTable");

Is there any function with similar functionality?

score 8 · Accepted Answer · answered Apr 08 '14 at 08:36

To check if table exists, check if any rows are returned by the following query:

SELECT name FROM sqlite_master WHERE type='table' AND name='your_table_name_here'

To run the query & check if rows exist, here is the code:

sqlite3_stmt *pSelectStatement = NULL;
int iResult = SQLITE_ERROR;
bool ret = false;

iResult = sqlite3_prepare16_v2(m_pDb, query, -1, &pSelectStatement, 0);

if ((iResult == SQLITE_OK) && (pSelectStatement != NULL))
{                   
    iResult = sqlite3_step(pSelectStatement);

    //was found?
    if (iResult == SQLITE_ROW) {
        ret = true;
        sqlite3_clear_bindings(pSelectStatement);
        sqlite3_reset(pSelectStatement);
    }
    iResult = sqlite3_finalize(pSelectStatement);
}

aisbaa · Answer 2 · 2014-04-08T08:58:32.990

-1

Also as alternative that would [probably] work on any SQL database select count(*) from table_that_might_exist limit 1.

edited Apr 08 '14 at 08:58

answered Apr 08 '14 at 08:41

aisbaa

9,867
6
33
48

That's really a brute force way to implement this. If the table exists and is large, it's probably slow, and you'll have to handle an exception for the case when the table doesn't exist. – Frank Schmitt Apr 08 '14 at 08:43
How about now, not slow anymore :D. Yet it is not very easy to execute in sqlite3. Also I wonder what would sqlite3_exec return if table does not exists. – aisbaa Apr 08 '14 at 08:59
1

Probably faster (with `LIMIT 1`), but won't work on all RDBMs (e.g. Oracle has no support for `LIMIT`) – Frank Schmitt Apr 08 '14 at 12:42

SQLite3 C interface: how to determine existence of a table

2 Answers2

Linked