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.

Accepted Answer

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().

Count the number of tables in the sqlite database

Solution

Problem