Drop all tables command

Question

What is the command to drop all tables in SQLite?

Similarly I'd like to drop all indexes.

Answer 1

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

Note: These commands have the potential to 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 then want to recover the deleted space with

VACUUM;

and a good test to make sure everything is ok

PRAGMA INTEGRITY_CHECK;

Answer 2

I don't think you can drop all tables in one hit but you can do the following to get the commands:

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

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

You can use other clauses in the where section to limit which tables or indexes are selected (such as "and name glob 'pax_*'" for those starting with "pax_").

You could combine the creation of this script with the running of it in a simple bash (or cmd.exe) script so there's only one command to run.

If you don't care about any of the information in the DB, I think you can just delete the file it's stored in off the hard disk - that's probably faster. I've never tested this but I can't see why it wouldn't work.

Answer 3

rm db/development.sqlite3

Answer 4

I had the same problem 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

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 6

I'd like to add to other answers involving dropping tables and not deleting the file, that you can also execute delete from sqlite_sequence to reset auto-increment sequences.

Answer 7

Once you've dropped all the tables (and the indexes will disappear when the table goes) then there's nothing left in a SQLite database as far as I know, although the file doesn't seem to shrink (from a quick test I just did).

So deleting the file would seem to be fastest - it should just be recreated when your app tries to access the db file.

Answer 8

I had this issue in Android and I wrote a method similar to it-west.

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 didn't want to 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();
    }
}

Answer 9

I can't say this is the most bulletproof or portable solution, but it works for my testing scripts:

.output /tmp/temp_drop_tables.sql
select 'drop table ' || name || ';' from sqlite_master where type = 'table';
.output stdout
.read /tmp/temp_drop_tables.sql
.system rm /tmp/temp_drop_tables.sql

This bit of code redirects output to a temporary file, constructs the 'drop table' commands that I want to run (sending the commands to the temp file), sets output back to standard out, then executes the commands from the file, and finally removes the file.

Answer 10

Or at a shell prompt, in just two lines, without a named temporary file, assuming $db is the SQLite database name:

echo "SELECT 'DROP TABLE ' || name ||';' FROM sqlite_master WHERE type = 'table';" |
    sqlite3 -readonly "$db" | sqlite3 "$db"

Answer 11

To delete also views add 'view' keyword:

delete from sqlite_master where type in ('view', 'table', 'index', 'trigger');