Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Drop all tables command

What is the command to drop all tables in SQLite?

Similarly I'd like to drop all indexes.

like image 581
alamodey Avatar asked Feb 08 '09 10:02

alamodey


People also ask

How do I remove all tables?

Select all tables from the left sidebar. Right-click and choose delete, or simply hit delete button. Press Cmd + S to commit changes to the server.

How do I drop all tables in SQL Server?

Press F7 to open Object Explorer Details view. In this view select tables which have to be deleted (in this case all of them) Keep pressing Delete until all tables have been deleted (you repeat it as many times as amount of errors due to key constraints/dependencies)

How do I drop all tables in MySQL terminal?

To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];


2 Answers

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; 
like image 144
Noah Avatar answered Oct 10 '22 05:10

Noah


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.

like image 31
paxdiablo Avatar answered Oct 10 '22 07:10

paxdiablo