How to remove all sqlite table indexes

Question

I have a simple question: how to delete all indexes of sqlite table? I have several indexes created with a random name.

Yours faithfully,
Pentium10

Answer 1

I do not know that you can reset ALL indexes in one command - IMO, you need to drop them by name. See Also: http://www.sqlite.org/lang_dropindex.html Also, read more information: Discard all table commands

Answer 2

To get all index names in the 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 iteration conceived the results and discarded them

 FOR x IN RESULTSET SQL = "DROP INDEX " & X 

Answer 3

 #!/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 that no other process is accessing the database while you are calling this script, or if this is not possible, add

 PRAGMA busy_timeout=20000; 

in every echo you send to the database

Answer 4

Here's how to do it in Android (using Robert 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); }