CODEWITHSUNDEEP
sqlite
Pentium10
Pentium10

Reputation: 207830

how to drop all indexes of a sqlite table

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

Regards,
Pentium10

Upvotes: 11

Views: 13081

Answers (4)

Boycott A.I.
Boycott A.I.

Reputation: 18871

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

Upvotes: 0

rubo77
rubo77

Reputation: 20817

#!/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

Upvotes: 0

Robert
Robert

Reputation: 38213

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

Upvotes: 22

mr-sk
mr-sk

Reputation: 13397

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

Upvotes: 4

Related Questions