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

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 wht it wouldn't work.

Answer 2

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 3

rm db/development.sqlite3

Answer 4

You can do it with the following DANGEROUS commands:

PRAGMA writable_schema = 1;
delete from sqlite_master where type = 'table';
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;