Count the number of tables in the sqlite database

Question

I need a count of the tables that are currently in my sqlite database. Tried searching but did not get any direct method.

I tried this method in my DbHelper.

public int countTables() {
    int count = 0;
    String SQL_GET_ALL_TABLES = "SELECT * FROM sqlite_master WHERE type='table'";
    Cursor cursor = getReadableDatabase()
            .rawQuery(SQL_GET_ALL_TABLES, null);
    cursor.moveToFirst();
    for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {

        count++;
        getReadableDatabase().close();

    }
    cursor.close();
    return count;
}

But this method gives me a wrong count. I have only 3 tables in my DB, but this method returns count as 5. What could be wrong with this method. Is there any direct method to get the count.

Answer 1

Your method is fine, you could even use SELECT count(*) .... But there are two tables created automatically android_metadata and sqlite_sequence, simply takes these into account: 5 - 2 = 3 tables that you created.

Or since Squonk provided excellent documentation about when sqlite_sequence is created and when it might not exist, I recommend this query:

SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name != 'android_metadata' AND name != 'sqlite_sequence';

A few pointers to shorten your code

All of this:

cursor.moveToFirst();
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {

Can be accomplished like this:

while(cursor.moveToNext()) {

Cursor.moveToNext() return true if the next row exists. If a new cursor is not empty moveToNext() will return true and set this index on the first position. When cursor.isAfterLast() == true, moveToNext() returns false.

But if you simply want to count how many rows are in the Cursor use:

int count = cursor.getCount();

Lastly, store your writable database in a variable.

getReadableDatabase().close();

I haven't tested this, but if getReadableDatabase() returns a new SQLiteDatabase object each time then you are simply closing this new database as soon as you have created it... I would close the database once and I would close it after I have closed all of the Cursors I retrieved from it.

SQLiteDatabase database = getReadableDatabase();
...

database.close();

If you are using a lot of queries consider opening your database in onResume() and closing it in onPause().