CODEWITHSUNDEEP
androidsqlite
Valentin Grégoire
Valentin Grégoire

Reputation: 1150

SQLite select all tables DESC

I have found the solution to get all tables in SQLite here. How to list the tables in an SQLite database file that was opened with ATTACH? However, when I change: 

SELECT name FROM sqlite_master WHERE type='table';

into: 

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

the output is completely weird. The first query gives me: tbl201306 --> only 1 table so far, for June 2013. The second query gives me: android_metadata --> the name is not changed, but it returns this name.

I want to have these tables in descending order because in the future, the newest table would be on the top then.

My complete code: 

public ArrayList<String> getDBTables() {
    ArrayList<String> toReturn = new ArrayList<String>();
    try {
        Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name DESC", null);
        c.moveToFirst();

        while (c.moveToNext()) {
            toReturn.add(c.getString(c.getColumnIndex("name")));
        }
        c.close();
    }
    catch(SQLiteException e) {
        e.printStackTrace();
    }
    return toReturn;
}

Upvotes: 1

Views: 1504

Answers (2)

Valentin Gr&#233;goire
Valentin Gr&#233;goire

Reputation: 1150

This is the ultimate solution I used: 

public ArrayList<String> getDBTables() {
    ArrayList<String> toReturn = new ArrayList<String>();
    try {
        Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'tbl%' ORDER BY name DESC", null);
        while(c.moveToNext()) {
            toReturn.add(c.getString(c.getColumnIndex("name")));
        }
        c.close();
    }
    catch(SQLiteException e) {
        e.printStackTrace();
    }
    return toReturn;
}

Upvotes: 0

CL.
CL.

Reputation: 180270

Your code skips over the first returned record.

You can either

  • keep the call to moveToFirst(), and change the loop into a do { ... } while (); loop, or
  • just remove the call to moveToFirst().

Upvotes: 1

Related Questions