Delete all tables from sqlite database

Question

I have done a lot of research and was unable to find a suitable method to delete all the tables in an SQLite database. Finally, I did a code to get all table names from the database and I tried to delete the tables using the retrieved table names one by one. It didn't work as well.

Please suggest me a method to delete all tables from the database.

This is the code that I used:

public void deleteall(){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
    do
    {
        db.delete(c.getString(0),null,null);
    }while (c.moveToNext());
}

function deleteall() is called on button click whos code is given as below:

public void ButtonClick(View view)
{
    String Button_text;
    Button_text = ((Button) view).getText().toString();

    if(Button_text.equals("Delete Database"))
    {
        DatabaseHelper a = new DatabaseHelper(this);
        a.deleteall();
        Toast.makeText(getApplicationContext(), "Database Deleted Succesfully!", Toast.LENGTH_SHORT).show();
    }}

Can't you just delete the whole database file? stackoverflow.com/questions/4406067/… — Thilo
– Thilo, Commented Jul 30, 2016 at 9:59
@TimBiegeleisen: I want to remove all tables from the database, not just delete the data in the tables.Please suggest me a method to remove all the tables. — Tony Mathew
– Tony Mathew, Commented Jul 30, 2016 at 10:16
Check with accepted answer in this link.stackoverflow.com/questions/25641350/… — Pravin Divraniya
– Pravin Divraniya, Commented Jul 30, 2016 at 11:12

Tim Biegeleisen · Accepted Answer · 2020-12-18 23:39:55Z

26

Use DROP TABLE:

// query to obtain the names of all tables in your database
Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
List<String> tables = new ArrayList<>();

// iterate over the result set, adding every table name to a list
while (c.moveToNext()) {
    tables.add(c.getString(0));
}

// call DROP TABLE on every table name
for (String table : tables) {
    String dropQuery = "DROP TABLE IF EXISTS " + table;
    db.execSQL(dropQuery);
}

edited Dec 18, 2020 at 23:39

answered Jul 30, 2016 at 10:28

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jon · Accepted Answer · 2018-10-09 11:12:07Z

Tim Biegeleisen's answer almost worked for me, but because I used AUTOINCREMENT primary keys in my tables, there was a table called sqlite_sequence. SQLite would crash when the routine tried to drop that table. I couldn't catch the exception either. Looking at https://www.sqlite.org/fileformat.html#internal_schema_objects, I learned that there could be several of these internal schema tables that I shouldn't drop. The documentation says that any of these tables have names beginning with sqlite_ so I wrote this method

private void dropAllUserTables(SQLiteDatabase db) {
    Cursor cursor = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
    //noinspection TryFinallyCanBeTryWithResources not available with API < 19
    try {
        List<String> tables = new ArrayList<>(cursor.getCount());

        while (cursor.moveToNext()) {
            tables.add(cursor.getString(0));
        }

        for (String table : tables) {
            if (table.startsWith("sqlite_")) {
                continue;
            }
            db.execSQL("DROP TABLE IF EXISTS " + table);
            Log.v(LOG_TAG, "Dropped table " + table);
        }
    } finally {
        cursor.close();
    }
}

Ganesh Karewad · Accepted Answer · 2016-07-30 10:34:40Z

2

delete database instead of deleting tables and then create new with same name if you need. use following code

context.deleteDatabase(DATABASE_NAME); 
          or
context.deleteDatabase(path);

answered Jul 30, 2016 at 10:34

Ganesh Karewad

1,22813 silver badges21 bronze badges

Comments

atlascoder · Accepted Answer · 2017-02-12 01:41:43Z

2

For me, the working solution is:

    Cursor c = db.rawQuery(
            "SELECT name FROM sqlite_master WHERE type IS 'table'" +
                    " AND name NOT IN ('sqlite_master', 'sqlite_sequence')",
            null
    );
    if(c.moveToFirst()){
        do{
            db.execSQL("DROP TABLE " + c.getString(c.getColumnIndex("name")));
        }while(c.moveToNext());
    }

edited Feb 12, 2017 at 1:41

answered Feb 11, 2017 at 14:41

atlascoder

3,0933 gold badges29 silver badges40 bronze badges

Collectives™ on Stack Overflow

Delete all tables from sqlite database

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related