SQLite select all tables DESC

Question

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;
}

Wow, that doesn't make much sense. That looks like valid code. And your sql line runs fine on my DB. — HalR, Jun 13 '13 at 04:50
When I have weird stuff like this happen I try "reality checks". If you change back to the first line does it still work? Your database could be messed up. Other "desperate" measures would include deleting the current string, retyping it, and doing a fully clean rebuild on your project, including removing it first from the device. — HalR, Jun 13 '13 at 04:57
What is your problem? The query returns two records. The names are in descending order. What do you expect? Do you have more tables in the database? — cha, Jun 13 '13 at 05:10
When I use the first SQL, I do get the original name (tbl201306) again. I have no clue why it is. I run it to populate a spinner. I could reverse the order in my ArrayList later on, but that's not that efficient. For now there is only 1 record in my db btw. — Valentin Grégoire, Jun 13 '13 at 13:39

score 1 · Accepted Answer · answered Jun 13 '13 at 06:51

1

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

answered Jun 13 '13 at 06:51

CL.

173,858
17
217
259

You were absolutely right! Apparently the android_metadata table is generated automatically. So there were 2 tables, and I skipped the first table. – Valentin Grégoire Jun 13 '13 at 13:49

score 0 · Answer 2 · answered Jun 13 '13 at 13:51

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;
}

SQLite select all tables DESC

2 Answers2