Delete From All Tables in SQLite

Question

Is there a SQL command in SQLite (for C#.NET) which performs the equivalent of this?

DELETE * FROM * WHERE id='idnumber'

Answer 1

No, there's not.

One way is to write a little script to query metadata tables to create another script with a series of individual delete statements, one per table.

An example could be (assuming every table had an ID column):

select 'delete from ' || name || ' where ID = 42'
from sqlite_master
where type = 'table'

This would generate a delete statement for each table which you could then capture and run as a script.

But it's generally a good idea for database developers to know the names of their tables :-)

Answer 2

This remove will wipe all content from your database:

.output wipe.sql
.print BEGIN TRANSACTION;
SELECT 'DELETE FROM ' || name || ' WHERE id="idnumber";'
FROM sqlite_master
WHERE type = 'table';
.print COMMIT;
.print VACUUM;
.output
-- .read wipe.sql

Beware that if you leave out the WHERE id="idnumber" this will wipe your database!

Answer 3

A method that I use for dynamic programmatic purposes is creating an array of the table names and loop through them.

e.g.

Swift

var array = ['table1','table2','table3']

for item in array

{
    
var stringToDeleteTables:String = "DELETE FROM \\(item) [optional where clause]"

//run the command on the database

}

I hope this helps someone. Great for things such as sign outs or clearing search data. You can even make an extension of an array and call it as a function.

Answer 4

You can use try it :

//You can do it with the following DANGEROUS commands:

PRAGMA writable_schema = 1;
delete from sqlite_master where type = 'table';
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;

Hope it will be useful for you.