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.
SQL: delete all the data from all available tables
Generate a script to truncate (= remove all rows from) all tables:
select 'truncate table ' || table_name || ';' from user_tables
And then execute the script.
sql server: delete all the rows of all the tables
If you don't want to script and drop the tables, there are a number of ways to do this with a loop. Here's probably the easiest:
sp_MsForEachTable 'TRUNCATE TABLE ?'
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.
Delete data from all tables in MYSQL
I don't think so (but I've been wrong before). What I tend to do is those cases is a two-step process.
If your DBMS has a command line interface, you can use it to create a script to do the bulk of the work, something like:
db2 "select 'db2 delete from ' | tblname from sysibm.systables
where owner = 'pax'" >p2.sh
p2.sh
The first bit simply creates a p2.sh
file (or a p2.cmd
file under Windows) containing a delete from
statement for every table owned by pax
. Then you just run that command file to do the dirty work. You may want to check it first, of course :-)
Not the one-step process you were looking for but still very simple. I'm assuming here that mysql also has a command line interface.
Update:
The MySQL version of the above looks like it should be:
echo "select 'mysql truncate table ' | table_name
from information_schema.tables" | mysql >p2.sh
bash p2.sh
This uses the truncate
command which is usually more efficient than delete from
for deleting all rows. It also uses the proper MySQL system tables to get the table names.
One point though - you may want to put a where
clause on that select to limit the tables to those you want deleted. The query as it stands will try to delete every table. One possibility is to limit it with specific table_schema
and/or table_type
values.
Related Topics
Watching Variables in Ssis During Debug
Rails Includes with Conditions
How to Select Rows for a Specific Date, Ignoring Time in SQL Server
SQL Join Where to Place the Where Condition
How to Drop All Foreign Key Constraints in All Tables
MySQL Statement Combining a Join and a Count
Get All Characters Before Space in MySQL
Delete with "Join" in Oracle SQL Query
Comparing Results with Today's Date
How to Format Bigint Field into a Date in Postgresql
Need SQL Query to Find Parent Records Without Child Records
Unit Testing Framework for Oracle Pl/Sql
How to Sort a Linked List in SQL
SQL Statement with Multiple Sets and Wheres
How to Count Number of Occurrences for All Different Values in Database Column