Reputation: 14243
How to drop all tables from a database with one SQL query?
I don't want to type all tables' name to drop all of them. Is it possible with one query?
Upvotes: 284
Views: 1033123
Answers (15)
Reputation: 11
Delete all tables without entering the name of each table
DECLARE @disable_constraints NVARCHAR(MAX) = N'' SELECT @disable_constraints += N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' NOCHECK CONSTRAINT ALL; ' FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
EXEC sp_executesql @disable_constraints
DECLARE @drop_tables NVARCHAR(MAX) = N'' SELECT @drop_tables += N'DROP TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'; ' FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
EXEC sp_executesql @drop_tables
DECLARE @enable_constraints NVARCHAR(MAX) = N'' SELECT @enable_constraints += N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' WITH CHECK CHECK CONSTRAINT ALL; ' FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
EXEC sp_executesql @enable_constraints
Upvotes: 0
Reputation: 1885
The simplest way is to drop the whole database and create it once again:
drop database db_name
create database db_name
That's all.
As pointed out in the comments, this does have some important side-effects to be aware of (depending on your needs, these effects may or may not be desirable):
- This will wipe off all the other system objects like Procedures, views etc., associated the table.
- You'll lose any users you added to the database.
Upvotes: 53
Reputation: 13701
This solution worked for me in Azure SQL on a database with tables in multiple schema's, including system versioned tables.
It uses a cursor to go through all constraints and tables, so it won't end up in an endless loop if you would get an error somewhere.
--drop all FK's
DECLARE @tableName VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
DECLARE @SchemaName varchar(254)
DECLARE cur CURSOR FOR SELECT c.TABLE_NAME,c.CONSTRAINT_SCHEMA, c.CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c WHERE c.CONSTRAINT_CATALOG=DB_NAME() AND c.CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME
OPEN cur
FETCH NEXT FROM cur INTO @tableName, @SchemaName, @constraint
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQL = 'ALTER TABLE [' + RTRIM(@SchemaName) + '].[' + RTRIM(@tableName) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
PRINT 'dropped constraint : ' + @constraint
FETCH NEXT FROM cur INTO @tableName, @SchemaName, @constraint
END
CLOSE cur
DEALLOCATE cur
GO
--Set system versioning off
DECLARE @tableName VARCHAR(128)
DECLARE @SQL VARCHAR(254)
DECLARE @SchemaName varchar(254)
DECLARE cur CURSOR FOR select t.name as TableName, schema_name(t.schema_id) as SchemaName from sys.tables t where t.temporal_type = 2
OPEN cur
FETCH NEXT FROM cur INTO @tableName, @SchemaName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQL = 'ALTER TABLE [' + RTRIM(@SchemaName) + '].[' + RTRIM(@tableName) +'] SET (SYSTEM_VERSIONING = OFF);'
EXEC (@SQL)
PRINT 'Removed versioning from: ' + @tableName
FETCH NEXT FROM cur INTO @tableName, @SchemaName
END
CLOSE cur
DEALLOCATE cur
GO
--Drop all tables
DECLARE @tableName VARCHAR(128)
DECLARE @SQL VARCHAR(254)
DECLARE @SchemaName varchar(254)
DECLARE cur CURSOR FOR SELECT t.TABLE_NAME, t.TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES t where t.TABLE_TYPE = 'BASE TABLE'
OPEN cur
FETCH NEXT FROM cur INTO @tableName, @SchemaName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQL = 'DROP TABLE [' + RTRIM(@SchemaName) + '].[' + RTRIM(@tableName) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @tableName
FETCH NEXT FROM cur INTO @tableName, @SchemaName
END
CLOSE cur
DEALLOCATE cur
GO
Upvotes: 1
Reputation: 1729
You could also use the following script to drop everything, including the following:
- non-system stored procedures
- views
- functions
- foreign key constraints
- primary key constraints
- system_versioning
- tables
https://michaelreichenbach.de/posts/how-to-drop-everything-in-a-mssql-database/
/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Procedure: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped View: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Function: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint is not null
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
EXEC (@SQL)
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Remove system versioning */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
IF OBJECTPROPERTY(OBJECT_ID(' + @name + '), 'TableTemporalType') = 2
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] SET (SYSTEM_VERSIONING = OFF); ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP PERIOD FOR SYSTEM_TIME;'
EXEC (@SQL)
PRINT 'System Versioning Disabled for Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
Upvotes: 32
Reputation: 65
I‘d do it with a loop for all tables if you’re using like oracle or sqlite:
FOR i IN (SELECT ut.table_name
FROM USER_TABLES ut) LOOP
EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
END LOOP;
Upvotes: 0
Reputation: 405
For me I just do
DECLARE @cnt INT = 0;
WHILE @cnt < 10 --Change this if all tables are not dropped with one run
BEGIN
SET @cnt = @cnt + 1;
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
END
Upvotes: -1
Reputation: 2656
If anybody else had a problem with best answer's solution (including disabling foreign keys), here is another solution from me:
-- CLEAN DB
USE [DB_NAME]
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + 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
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
Upvotes: 7
Reputation: 3638
If you want to use only one SQL query to delete all tables you can use this:
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
This is a hidden Stored Procedure in sql server, and will be executed for each table in the database you're connected.
Note: You may need to execute the query a few times to delete all tables due to dependencies.
Note2: To avoid the first note, before running the query, first check if there foreign keys relations to any table. If there are then just disable foreign key constraint by running the query bellow:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Upvotes: 120
Reputation: 93694
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.
Upvotes: 262
Reputation: 312
I Know this question is very old but every Time i need this code .. by the way if you have tables and views and Functions and PROCEDURES you can delete it all by this Script ..
so why i post this Script ?? because if u delete all tables you will need to delete all views and if you have Functions and PROCEDURES you need to delete it too
i Hope it will help someone
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
DECLARE @sql VARCHAR(MAX) = ''
, @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;
SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' +
QUOTENAME(v.name) +';' + @crlf
FROM sys.views v
PRINT @sql;
EXEC(@sql);
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop procedure [' + @procName + ']')
fetch next from cur into @procName
end
close cur
deallocate cur
Declare @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N' DROP FUNCTION '
+ QUOTENAME(SCHEMA_NAME(schema_id))
+ N'.' + QUOTENAME(name)
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
Exec sp_executesql @sql
GO
Upvotes: 1
Reputation: 41
Use the following script to drop all constraints:
DECLARE @sql NVARCHAR(max)=''
SELECT @sql += ' ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT all; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Exec Sp_executesql @sql
Then run the following to drop all tables:
select @sql='';
SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Exec Sp_executesql @sql
This worked for me in Azure SQL Database where 'sp_msforeachtable' was not available!
Upvotes: 4
Reputation: 6771
If you don't want to type, you can create the statements with this:
USE Databasename
SELECT 'DROP TABLE [' + name + '];'
FROM sys.tables
Then copy and paste into a new SSMS window to run it.
Upvotes: 64
Reputation: 2079
Not quite 1 query, still quite short and sweet:
-- Disable all referential integrity constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
-- Drop all PKs and FKs
declare @sql nvarchar(max)
SELECT @sql = STUFF((SELECT '; ' + 'ALTER TABLE ' + Table_Name +' drop constraint ' + Constraint_Name from Information_Schema.CONSTRAINT_TABLE_USAGE ORDER BY Constraint_Name FOR XML PATH('')),1,1,'')
EXECUTE (@sql)
GO
-- Drop all tables
EXEC sp_msforeachtable 'DROP TABLE ?'
GO
Upvotes: 4
Reputation: 3691
As a follow-up to Dave.Gugg's answer, this would be the code someone would need to get all the DROP TABLE lines in MySQL:
SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = 'your_database_name'
Upvotes: 27
Reputation: 3443
I'd just make a small change to @NoDisplayName's answer and use QUOTENAME() on the TABLE_NAME column and also include the TABLE_SCHEMA column encase the tables aren't in the dbo schema.
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;
Or using sys schema views (as per @swasheck's comment):
DECLARE @sql nvarchar(max) = '';
SELECT @sql += 'DROP TABLE ' + QUOTENAME([S].[name]) + '.' + QUOTENAME([T].[name]) + ';'
FROM [sys].[tables] AS [T]
INNER JOIN [sys].[schemas] AS [S] ON ([T].[schema_id] = [S].[schema_id])
WHERE [T].[type] = 'U' AND [T].[is_ms_shipped] = 0;
EXEC SP_EXECUTESQL @sql;
Upvotes: 9
Related Questions
- How to drop all tables in a SQL Server database?
- Drop Several Tables at Once
- How can I drop multiple tables (but not all) in one database?
- How to drop all tables except 1
- Drop all the tables in a DB from Microsoft SQL server management studio
- Drop large set of tables in SQL Server
- Drop a selection of tables
- SQL Server 2005: How to drop selected tables, with all related objects
- How to drop all tables from a database in sql server 2005 in a statement except one table
- Dropping a group of tables in SQL Server