CODEWITHSUNDEEP
androidsqlite
darsh
darsh

Reputation: 751

Count the number of tables in the sqlite database

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.

Upvotes: 10

Views: 19710

Answers (3)

Rahul Chhangani
Rahul Chhangani

Reputation: 39

Find count and table names

find count

ResultSet rs = st.executeQuery("select count(*) from sqlite_master as tables where type='table'");
System.out.println(rs.getInt(1));

retrieve table names

ResultSet rs2 = st.executeQuery("select name from sqlite_master as tables where type='table'");
    while(rs2.next())
    {
          String tid = rs2.getString(1);
          System.out.println(tid);
    }
    rs.close();

Upvotes: 1

Sam
Sam

Reputation: 86948

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

Upvotes: 26

Chintan Raghwani
Chintan Raghwani

Reputation: 3370

Try

              vvvvvv
SELECT * FROM YourDataBaseName.sqlite_master WHERE type='table';

Just put your DatabseName as shown.

Upvotes: 0

Related Questions