Drop all tables command

sql

database-management

sqlite

sql-commands

byAlex Kataev·Oct 22, 2024

To drop all tables in SQL Server, use this one-liner script:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += 'DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '; '
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id;

EXEC sp_executesql @sql;

These lines would execute all DROP TABLE commands in one shot. Be sure you've backup as these would erase all tables for good.

SQLite - dropping all tables

When using SQLite, which doesn't provide a DROP ALL TABLES command, there're a number of steps you need to take. It's quite important to remember to backup your database beforehand, as these steps are destructive and one mistake can result in data loss or corruption:

-- Make the schema writable (Like saying "Hold my beer!")
PRAGMA writable_schema = 1;

-- Goodbye tables and indexes, you will not be missed
DELETE FROM sqlite_master WHERE type IN ('table', 'index');

-- Just kidding, let's have a sober discussion now
PRAGMA writable_schema = 0;

-- Maybe cleanup after dropping the tables?
VACUUM;

-- Just making sure all gears are working properly
PRAGMA integrity_check;

These steps will ensure all user tables and indexes are removed, but beware of special tables like 'sqlite_sequence', they're kind of special, so go easy on them.

Scripting your way through table destruction

If you need to work with databases that do not have a single DROP ALL TABLES command, hold onto your hat, time for some scripting! Using bash on Linux systems or cmd.exe on Windows, you can make the process of dropping tables as easy as making a toast:

Bash example - sinister laugher included:

sqlite3 db/development.sqlite3 ".tables" | tr ' ' '\n' | xargs -I {} echo "DROP TABLE '{}';" > drop_tables.sql
sqlite3 db/development.sqlite3 < drop_tables.sql

Windows CMD Example - now, for the dark side users:

FOR /F "tokens=*" %i IN ('sqlite3 db/development.sqlite3 ".tables"') DO sqlite3 db/development.sqlite3 "DROP TABLE [%i];"

All things SQLITE

Being effective with SQLite is all about understanding the ins and outs of command syntax and how the database system behaves. Here are a few best practices:

Special tables: Some tables believe they are 'special', like sqlite_sequence. Leave them alone for good reasons.
sqlite3's executescript: to run multiple SQL statements at once. Perfect for SQL marathons.
Conditional table deletion: To avoid crying over non-existing tables, use DROP TABLE IF EXISTS.
Integrity Checks: After actions like table dropping, use PRAGMA integrity_check for post-op checkups.
Cursor handling: after finishing with the cursor, it's good manners (and good for your memory) to close it.

Practical aspects to remember

Transactions aren't a waste

Whenever you're modifying a database, especially doing something as drastic as dropping all tables, a good practice is to use transactions. That's your safety net:

BEGIN TRANSACTION;

-- Place for your "Drop tables" rave party

COMMIT;

Be selective about your destruction

Not all tables deserve to die—a few, like metadata or system tables, may actually be useful. You can use the WHERE clause in your SQL statements to single out those worth sparing.