Drop all tables command

sql sqlite rdbms database
alamodey picture alamodey · Feb 8, 2009 · Viewed 124k times · Source

What is the command to drop all tables in SQLite?

Similarly I'd like to drop all indexes.

Answer

paxdiablo picture paxdiablo · Feb 8, 2009

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.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More