Cancel the command of all tables

Question

What is the command to delete all tables in SQLite?

Similarly, I would like to delete all indexes.

Answer 1

rm db/development.sqlite3 

Answer 2

I don’t think you can delete all tables with one hit, but you can do the following to get the commands:

 select 'drop table ' || name || ';' from sqlite_master where type = 'table'; 

The result of this is a script that will output the tables for you. For indexes, simply replace the table with an index.

You can use other clauses in the where clause to limit which tables or indexes are selected (for example, " and name glob 'pax_*' " for those starting with "pax _").

You could combine the creation of this script with its launch in a simple bash (or cmd.exe) script, so only one command will work.

If you don't care about any information in the database, I think you can just delete the file that it stores on your hard drive - which is probably faster. I have never tested this, but I do not understand why this will not work.

Answer 3

Although it is true that there is no DROP ALL TABLES command, you can use the following set of commands.

Note. These commands can corrupt your database, so make sure you have a backup.

 PRAGMA writable_schema = 1; delete from sqlite_master where type in ('table', 'index', 'trigger'); PRAGMA writable_schema = 0; 

you want to recover deleted space with

 VACUUM; 

and a good test to make sure everything is in order

 PRAGMA INTEGRITY_CHECK; 

Answer 4

I had the same issue with SQLite and Android. Here is my solution:

 List<String> tables = new ArrayList<String>(); Cursor cursor = db.rawQuery("SELECT * FROM sqlite_master WHERE type='table';", null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { String tableName = cursor.getString(1); if (!tableName.equals("android_metadata") && !tableName.equals("sqlite_sequence")) tables.add(tableName); cursor.moveToNext(); } cursor.close(); for(String tableName:tables) { db.execSQL("DROP TABLE IF EXISTS " + tableName); } 

Answer 5

I would like to add to the other answers related to dropping tables and not delete the file so that you can also execute delete from sqlite_sequence to reset sequences with auto-increment.

Answer 6

Using pysqlite:

 tables = list(cur.execute("select name from sqlite_master where type is 'table'")) cur.executescript(';'.join(["drop table if exists %s" %i for i in tables])) 

Answer 7

As soon as you drop all the tables (and the indexes disappear when this table goes), then there is nothing left in the SQLite database, as far as I know, although the file does not seem to be shortened (from the quick test I just did).

Thus, deleting the file will be the fastest - you just need to recreate it when your application tries to access the db file.

Answer 8

I had this problem in android and I wrote a method similar to it-west.

Since I used AUTOINCREMENT primary keys in my tables, there was a table called sqlite_sequence . SQLite will work when a program tries to delete this table. I also could not catch the exception. Looking at https://www.sqlite.org/fileformat.html#internal_schema_objects , I learned that there may be several such internal schema tables that I would not want to discard. The documentation says that any of these tables have names starting 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(); } }