How to drop all tables in a SQL Server database?

Question

I'm trying to write a script that will completely empty a SQL Server database. This is what I have so far:

USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'

When I run it in the Management Studio, I get:

Command(s) completed successfully.

but when I refresh the table list, they are all still there. What am I doing wrong?

Answer 1

You can also delete all tables from a database using only the SSMS UI tools (without using a SQL script). Sometimes, this method can be more convenient, especially if it is performed occasionally.

I do this step-by-step as follows:

1. Select "Tables" in the database tree (Object Explorer).
2. Press F7 to open the Object Explorer Details view.
3. In this view, select the tables that need to be deleted (in this case, all of them).
4. Keep pressing Delete until all tables have been deleted (you may need to repeat this as many times as there are errors due to key constraints or dependencies).

Answer 2

This one works perfectly :

-- use database
USE [DataBaseName]
GO
  
-- drop constraints
DECLARE @DropConstraints NVARCHAR(max) = ''
SELECT @DropConstraints += 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
                        +  QUOTENAME(OBJECT_NAME(parent_object_id)) + ' ' + 'DROP CONSTRAINT' + QUOTENAME(name)
FROM sys.foreign_keys
EXECUTE sp_executesql @DropConstraints;
GO
  
-- drop tables
DECLARE @DropTables NVARCHAR(max) = ''
SELECT @DropTables += 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
EXECUTE sp_executesql @DropTables;
GO

Answer 3

The accepted answer doesn't support Azure. It uses an undocumented stored procedure "sp_MSforeachtable". If you get an "azure could not find stored procedure 'sp_msforeachtable" error when running or simply want to avoid relying on undocumented features (which can be removed or have their functionality changed at any point) then try the below.

This version ignores the entity framework migration history table "__MigrationHistory" and the "database_firewall_rules" which is an Azure table you will not have permission to delete.

Lightly tested on Azure. Do check to make this this has no undesired effects on your environment.

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    EXEC(@sql)
    PRINT @sql
END

WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules' AND TABLE_TYPE != 'VIEW'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules' 
    EXEC(@sql)
    PRINT @sql
END

Taken from:

https://edspencer.me.uk/2013/02/25/drop-all-tables-in-a-sql-server-database-azure-friendly/

http://www.sqlservercentral.com/blogs/sqlservertips/2011/10/11/remove-all-foreign-keys/

Answer 4

sp_msforeachtable is not available in Azure SQL

For Azure SQL:

This query will drop Foreign Key constraints

DECLARE @Name VARCHAR(200)
DECLARE @Constraint VARCHAR(300)
DECLARE @SQL VARCHAR(300)

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

This will drop all the tables from the database

DECLARE @Name VARCHAR(200)
DECLARE @SQL VARCHAR(300)

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) +']' /*here you can change schema if it is different from dbo*/
    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

Azure SQL + tables (with constraints) in a different schema than dbo + ipv6_database_firewall_rules condition.

This is a little extension for https://stackoverflow.com/a/43128914/4510954 answer.

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    SELECT TOP 1 @sql=('ALTER TABLE ' + CONSTRAINT_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    EXEC(@sql)
    PRINT @sql
END

WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules' AND TABLE_NAME != 'ipv6_database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + CONSTRAINT_SCHEMA  + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END

Answer 6

Spot on!!

You can use below query to remove all the tables from database

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Happy coding !

Answer 7

For Temporal Tables it is a bit more complicated due to the fact there may be some foreign keys and also exception:

Drop table operation failed on table XXX because it is not a supported operation on system-versioned temporal tables

What you can use is:

-- Disable constraints (foreign keys)
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Disable system versioning (temporial tables)
EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableTemporalType'') = 2
  ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)
'
GO

-- Removing tables
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

Answer 8

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.

Answer 9

In SSMS:

• Right click the database
• Go to "Tasks"
• Click "Generate Scripts"
• In the "Choose Objects" section, select "Script entire database and all database objects"
• In the "Set Scripting Options" section, click the "Advanced" button
• On "Script DROP and CREATE" switch "Script CREATE" to "Script DROP" and press OK
• Then, either save to file, clipboard, or new query window.
• Run script.

Now, this will drop everything, including the database. Make sure to remove the code for the items you don't want dropped. Alternatively, in the "Choose Objects" section, instead of selecting to script entire database just select the items you want to remove.

Answer 10

For me, the easiest way:

--First delete all constraints

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.'
+ QUOTENAME(t.name) + N' DROP CONSTRAINT '
+ QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s 
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

EXEC sys.sp_executesql @sql;

-- Then drop all tables

exec sp_MSforeachtable 'DROP TABLE ?'

Answer 11

I know this is an old post now but I have tried all the answers on here on a multitude of databases and I have found they all work sometimes but not all of the time for various (I can only assume) quirks of SQL Server.

Eventually I came up with this. I have tested this everywhere (generally speaking) I can and it works (without any hidden store procedures).

For note mostly on SQL Server 2014. (but most of the other versions I tried it also seems to worked fine).

I have tried while loops and nulls etc etc, cursors and various other forms but they always seem to fail on some databases but not others for no obvious reason.

Getting a count and using that to iterate always seems to work on everything Ive tested.

USE [****YOUR_DATABASE****]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Drop all referential integrity constraints --
-- Drop all Primary Key constraints.          --

DECLARE @sql  NVARCHAR(296)
DECLARE @table_name VARCHAR(128)

DECLARE @constraint_name VARCHAR(128)
SET @constraint_name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME

WHILE @row_number > 0
BEGIN
    BEGIN
        SELECT TOP 1 @table_name = tc2.TABLE_NAME, @constraint_name = rc1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
        LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME
        AND rc1.CONSTRAINT_NAME > @constraint_name
        ORDER BY rc1.CONSTRAINT_NAME
        SELECT @sql = 'ALTER TABLE [dbo].[' + RTRIM(@table_name) +'] DROP CONSTRAINT [' + RTRIM(@constraint_name)+']'
        EXEC (@sql)
        PRINT 'Dropped Constraint: ' + @constraint_name + ' on ' + @table_name
        SET @row_number = @row_number - 1
    END
END
GO

-- Drop all tables --

DECLARE @sql  NVARCHAR(156)
DECLARE @name VARCHAR(128)
SET @name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM sysobjects WHERE [type] = 'U' AND category = 0

WHILE @row_number > 0
BEGIN
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
    SELECT @sql = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@sql)
    PRINT 'Dropped Table: ' + @name
    SET @row_number = @row_number - 1
END
GO

Answer 12

The fasted way is:

1. New Database Diagrams
2. Add all table
3. Ctrl + A to select all
4. Right Click "Remove from Database"
5. Ctrl + S to save
6. Enjoy

Answer 13

Short and sweet:

USE YOUR_DATABASE_NAME
-- 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 14

Seems the command should be without the square blanket

EXEC sp_msforeachtable 'drop table ?'

Answer 15

How about dropping the entire database and then creating it again? This works for me.

DROP DATABASE mydb;
CREATE DATABASE mydb;

Answer 16

delete is used for deleting rows from a table. You should use drop table instead.

EXEC sp_msforeachtable 'drop table [?]'

Answer 17

You are almost right, use instead:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DROP TABLE ?'

but second line you might need to execute more then once until you stop getting error:

Could not drop object 'dbo.table' because it is referenced by a FOREIGN KEY constraint.

Message:

Command(s) completed successfully.

means that all table were successfully deleted.

Answer 18

/* 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