Getting list of table names from database in Android application

Question

I am trying to get a list of table names from an SQLite database. I manage to get this, but I also get temporary table names like 'android_metadata' and 'sqlite_sequence'. I want to exclude these 2.

However, I can't get it to work right.

SQLiteDatabase database = 
getBaseContext().openOrCreateDatabase("vocabularyDatabase", 
MODE_PRIVATE, null);

    Cursor c = database.rawQuery(  "SELECT name FROM sqlite_master 
      WHERE (type = 'table') AND (name NOT LIKE 'sqlite_sequence' OR 
      name NOT LIKE 'android_metadata') ", 
      null);

    if (c.moveToFirst()){
        while (!c.isAfterLast() ){
                listOfWords.add(c.getString(c.getColumnIndex("name")) 
                 );
                c.moveToNext();

        }
    }

Well, I know the tables, but the idea is that the user of the app has made several lists stored as tables in a database, and should be able to see all the lists (tables), to be able to pick one of them. So this piece of code is just to test the code. — user820913, Oct 29 '17 at 19:21
That is what I thought - you should never create tables while running a program. A DB schema should be fixed and only data be added - not tables. — juergen d, Oct 29 '17 at 19:30
How about `SELECT name FROM sqlite_master WHERE (NOT (name = 'android_metadata' OR name LIKE '%sqlite_sequence%')) AND type = 'table'` — MikeT, Oct 29 '17 at 19:55
To Mike T: Didn´t work unfortunately. To juergen d: Why not let the user make a table? — user820913, Oct 30 '17 at 13:20

score 3 · Accepted Answer · answered Oct 29 '17 at 22:18

Assume that the current row is for android_metadata. The expression name NOT LIKE 'android_metadata' will be false, but the expression name NOT LIKE 'sqlite_sequence' will be true. So the WHERE clause reduces to true AND (true OR false), which is true.

You need to replace the OR with AND:

WHERE type = 'table'
  AND (name NOT LIKE 'sqlite_sequence' AND
       name NOT LIKE 'android_metadata')

If you really want to use OR, you have to apply DeMorgan's laws and negate the entire condition:

WHERE type = 'table'
  AND NOT (name LIKE 'sqlite_sequence' OR
           name LIKE 'android_metadata')

Alternatively, simply use IN with a list:

WHERE type = 'table'
  AND name NOT IN ('sqlite_sequence', 'android_metadata')

Getting list of table names from database in Android application

1 Answers1

Linked