Drop all tables command

Question

What is the command to drop all tables in SQLite    Similarly I d like to drop all indexes

User · Answer

I had the same problem with SQLite and Android  Here is my Solution   List lt String gt  tables   new ArrayList lt String gt     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    amp  amp               tableName equals  sqlite sequence            tables add tableName       cursor moveToNext      cursor close     for String tableName tables        db execSQL  DROP TABLE IF EXISTS     tableName

User · Answer

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

User · Answer

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

User · Answer

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  lt  19     try           List lt String gt  tables   new ArrayList lt  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

rm db development sqlite3

User · Answer

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

User · Answer

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

User · Answer

To delete also views add  view  keyword  delete from sqlite master where type in   view    table    index    trigger

[sql] Drop all tables command

Examples related to sql

Examples related to sqlite

Examples related to rdbms

Examples related to database