Drop all tables command

What is the command to drop all tables in SQLite?

Similarly I'd like to drop all indexes.

Answers


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.


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;

rm db/development.sqlite3

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);
}

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.


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.


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]))

Need Your Help

“End of Statement Expected” Error in VB 2010 in a do while statement

vb.net visual-studio-2010

I have a line of code that is working in one statement but not the second statement. I'm not sure what I'm doing wrong, so any help would be appreciated. I am learning slowly but surely, and it is...

AcquireCredentialsHandle returns massive expiration time

c# c++ windows winapi

I'm working on a .Net wrapper for the Windows SSPI API. Several API functions return expiration times, eg, for credential or security context expirations.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.