How to delete all tables from a database with a single SQL query?

Question

I do not want to type the name of all the tables in order to delete all of them. Is this possible with a single request?

Answer 1

Use the INFORMATION_SCHEMA.TABLES view to get a list of tables. Generate Drop scripts in the select statement and delete 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 foreign Keys defined between tables, first run the query below to disable all foreign keys in your database.

 EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" 

For more information, check here .

Answer 2

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 the sql server and will be executed for each table in the database to which you are connected.

Note. Before running on a query, first check to see if you have a foreign key relationship with any table. If you only have disable foreign key constraint by running this query

 EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" 

Answer 3

If you do not want to enter text, you can create instructions as follows:

 USE Databasename SELECT 'DROP TABLE [' + name + '];' FROM sys.tables 

Then copy and paste the SSMS into a new window to start it.

Answer 4

You can also use the following script to delete everything, including the following:

• unsystematic stored procedures
• Views
• the functions
• foreign key restrictions
• primary key restrictions
• tables

https://michaelreichenbach.de/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 /* 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 

Answer 5

I will just make a small change to @NoDisplayName's answer and use QUOTENAME() in the TABLE_NAME column, and also include the TABLE_SCHEMA column TABLE_SCHEMA that the tables are not in the dbo .

 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 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; 

Answer 6

As a follow-up to Dave.Gugg's answer, this will be the code someone would need to get all the DROP TABLE rows in MySQL:

 SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'your_database_name' 

Answer 7

If someone else had a problem with a better answer 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' 

Answer 8

The easiest way is to delete the entire database and create it again:

 drop database db_name create database db_name 

All this.

Answer 9

Not quite 1 request, 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 

Answer 10

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 everything to delete 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' not available!

Answer 11

I know this question is very old, but every time I need this code ... by the way, if you have tables and views, as well as Functions and PROCEDURES, you can delete all this with this script ... so why am i posting this script ?? because if you delete all the tables, you will need to delete all the views, and if you have functions and procedures, you need to delete it too
Hope this helps 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 

Answer 12

The fastest way is to reset and recreate the entire database. You can click on the script in SQL Management Studio:

• Right-click the database in the object explorer
• Select 'Script Database As'
• Select "Drop and Create to"
• Select your preferred target (new query window, file, etc.).