11

I have a simple question: How to drop all indexes of a sqlite table? I have multiple indexes created with random name.

Regards,
Pentium10

Pentium10
  • 204,586
  • 122
  • 423
  • 502

4 Answers4

22

To get all index names in a database

SELECT name FROM sqlite_master WHERE type == 'index'

For a specific table:

SELECT name FROM sqlite_master WHERE type == 'index' AND tbl_name == 'table Name'

Then in your language, iterate thought the results and drop them

FOR x IN RESULTSET
  SQL = "DROP INDEX " & X
Robert
  • 37,670
  • 37
  • 171
  • 213
3

I'm not aware that you can drop ALL indexes in one command - IMO you have to drop each by name. See also: http://www.sqlite.org/lang_dropindex.html Also, check this out for additional info: Drop all tables command

Community
  • 1
  • 1
mr-sk
  • 13,174
  • 11
  • 66
  • 101
0
#!/bin/bash

DB=your_sqlite.db
TABLE="some_table"

INDEXES="$(echo "SELECT name FROM sqlite_master WHERE type == 'index' AND tbl_name = '$TABLE;" | sqlite3 $DB)"
for i in $INDEXES; do
  echo "DROP INDEX '$i';" | sqlite3 $DB
done

Make sure no other process accesses the database, while you call this script, or if thats not possible add

PRAGMA busy_timeout=20000;

in each echo that you send to the database

rubo77
  • 19,527
  • 31
  • 134
  • 226
0

Here is how to do it in Android (with the help of Robert's answer and this SQLite page:

/*
 * Drop all indexes.
 */
try {
    Cursor cursor = db.rawQuery("SELECT name FROM sqlite_master WHERE type == 'index'", null);
    int numIndexes = (cursor == null) ? 0 : cursor.getCount();
    Log.d(LOG_TAG, "Num indexes to drop: " + numIndexes);
    if (numIndexes > 0) {
        String[] indexNames = new String[numIndexes];
        int i = 0;
        while (cursor.moveToNext()) {
            indexNames[i++] = cursor.getString(cursor.getColumnIndexOrThrow("name"));
        }

        for (i = 0; i < indexNames.length; i++) {
            Log.d(LOG_TAG, "Dropping index: " + indexNames[i] + "...");
            db.execSQL("DROP INDEX " + indexNames[i]);
            Log.e(LOG_TAG, "...index dropped!");
        }
    }
}
catch(Exception e) {
    Log.e(LOG_TAG, "Error dropping index", e);
}
ban-geoengineering
  • 18,324
  • 27
  • 171
  • 253