Drop all tables with exceptions in sqlite

Question

I saw this question where it is presented a command to drop all tables from an sqlite database. Is it possible to add exceptions, i.e., drop all tables except table X Y and Z?

Thanks

EDIT I also saw that it is possible in SQL. Is this script translatable to sqlite?

Matthias Wiehl · Accepted Answer · 2017-01-20T12:20:21.920

4

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type = 'table' AND name NOT IN ('X', 'Y', 'Z');
PRAGMA writable_schema = 0;
VACUUM;

Or, to get the DDL:

SELECT 'DROP TABLE ' || name || ';' FROM sqlite_master
    WHERE type = 'table' AND name NOT IN ('X', 'Y', 'Z');

edited Jan 20 '17 at 12:20

answered Jan 20 '17 at 12:13

Matthias Wiehl

1,799
16
22

1

If you have indexes on tables, this corrupts the whole database. – Daniel Jul 20 '20 at 16:48
brilliant. Would you update your answer to include double quotes around tables as well? Query fails if table names contains numbers – Amod Gokhale Oct 20 '21 at 10:32
SELECT 'DROP TABLE ''' || name || ''';' FROM sqlite_master WHERE type = 'table' AND name NOT IN ('X', 'Y', 'Z'); – Amod Gokhale Oct 20 '21 at 10:33

Drop all tables with exceptions in sqlite

1 Answers1