Drop All Tables Command

MySQL DROP all tables, ignoring foreign keys

I found the generated set of drop statements useful, and recommend these tweaks:

1. Limit the generated drops to your database like this:

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';

Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to cut and paste the output into your SQL engine to execute them.

Note 2: If you have VIEWs, you'll have to correct each DROP TABLE `VIEW_NAME` statement to DROP VIEW `VIEW_NAME` manually.

2. Note, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html, dropping with cascade is pointless / misleading:

"RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing."

Therefore, in order for the drop statements to work if you need:

SET FOREIGN_KEY_CHECKS = 0

This will disable referential integrity checks - so when you are done performing the drops you need, you will want to reset key checking with

SET FOREIGN_KEY_CHECKS = 1

3. The final execution should look like:

SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;

NB: to use output of SELECT easier, mysql -B option can help.

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Sys.Tables Version

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]
WHERE  t.type = 'U'

Exec sp_executesql @sql

Note: If you have any foreign Keys defined between tables then first run the below query to disable all foreign keys present in your database.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

For more information, check here.

How to drop all tables in a SQL Server database?

It doesn't work for me either when there are multiple foreign key tables.

I found that code that works and does everything you try (delete all tables from your database):

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

You can find the post here. It is the post by Groker.

Drop all tables command

rm db/development.sqlite3

How to remove all MySQL tables from the command-line without DROP database permissions?

You can generate statement like this: DROP TABLE t1, t2, t3, ... and then use prepared statements to execute it:

SET FOREIGN_KEY_CHECKS = 0; 
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'database_name'; -- specify DB name here.

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1; 

Drop all tables from database in single query in mysql

As far as I know you have to do it individually:
DROP TABLE user, settings, images

delete all from table

You can use the below query to remove all the rows from the table, also you should keep it in mind that it will reset the Identity too.

TRUNCATE TABLE table_name

How to delete all MySQL tables beginning with a certain prefix?

There's no single statement to do that.

The simplest approach is to generate a set of statements, and execute them individually.

A simple query can generate the statements for you:

 SELECT CONCAT('DROP TABLE `',t.table_schema,'`.`',t.table_name,'`;') AS stmt
   FROM information_schema.tables t
  WHERE t.table_schema = 'mydatabase'
    AND t.table_name LIKE 'aggregate\_temp%' ESCAPE '\\'
  ORDER BY t.table_name

That just returns a rowset, but the rows conveniently contain the exact SQL statements you need to execute. (Note that information_schema is a builtin database that contains metadata. You'd just need to replace mydatabase with the name of the database you want to drop tables from.

Save the resultset from this query as a plain text file, remove any heading line, and voila, you've got a script you can execute in your SQL client.

There's no need for an elaborate stored procedure.

How to delete all the tables in a MySQL database?

drop database YOUR_DATABASE;
/* this will delete all the tables for this database */

create database YOUR_DATABASE;
/* added back the database namespace */

---

Related Topics