Delete all data in SQL Server database

Asked 10/9, 2010 at 19:10 Answered 1/3 at 6:14

Solved sql sql-server delete-row multi-table-delete

152

How I can delete all records from all tables of my database? Can I do it with one SQL command or I need for one SQL command per one table?

Nestling answered 10/9, 2010 at 19:10 Comment(0)

227

SQLMenace's solution worked for me with a slight tweak to how data is deleted - DELETE FROM instead of TRUNCATE.

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

Contented answered 2/8, 2012 at 3:4 Comment(8)

Me To.. I was able to delete, but not to truncate. – Ramirez 8/10, 2012 at 8:54

It might also make sense to do a EXEC sp_MSForEachTable 'DBCC CHECKIDENT(''?'', RESEED, 0)' after the DELETE FROM to reset all the identity columns back to 0. – Hime 7/11, 2013 at 21:46

Its always a good start to the day when you find 6 lines of code that replaces 100s of delete statements! This method works without issue on SQL 2014 Express. – Phalarope 8/4, 2015 at 14:21

Don't forget to disable triggers aswell – Familist 27/1, 2016 at 10:37

I was getting error - DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.... For me worked: EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?' – Nourishment 1/3, 2016 at 9:43

azure db here, and error "Could not find stored procedure 'sp_MSForEachTable'." – Handball 30/10, 2018 at 13:32

Before I do this, and not being fully confident with SQL Server, I note that the answer doesn't have any indication as to which database. My SQL server has many databases, how can I be confident that it works on only the database I want? (I am most certainly not just going to give it a go and see what happens!!!) – Savell 16/8, 2021 at 2:28

on azure db, the procedure is "sp_MSforeachtable" (watch case) – Dodge 7/9, 2022 at 14:38

Usually I will just use the undocumented proc sp_MSForEachTable

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

Storfer answered 10/9, 2010 at 19:13 Comment(5)

I don't think this works. Looks like Kalen Delaney was inadvertently responsible for starting this idea off. Here she clarifies "you have to drop the referencing constraint in order to truncate the table." – Physical 2/10, 2010 at 0:38

Martin I just ran it 2 seconds ago in the Adventureworks DB without a problem – Storfer 2/10, 2010 at 0:45

It definitely does not work for me here.

create database testing; GO use testing; create table t1 (i int primary key) create table t2(i int primary key,p int references t1)

– Physical 2/10, 2010 at 1:19

This does not work, despite being marked as the answer. Setting nocheck constraint on foreign keys does not allow you to run truncate commands on those tables. You will still get errors that prevent you from truncating. – Apanage 7/8, 2013 at 19:28

this does not work due to the presence of foreign keys. Still I can't see why it was accepted as an answer :/ – Hage 15/9, 2014 at 9:30

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

Anywise answered 21/2, 2013 at 8:42 Comment(2)

interesting script, that does not make use of the undcoumented stored proc 'sp_MSForEachTable', which is missing on Azure. Needs tweaking if you have objects on another schema than [dbo], though. – Handball 30/10, 2018 at 13:56

Please use gist.github.com/metaskills/893599 to create sp_MSForEachTable in Azure – Anywise 31/10, 2018 at 4:33

I'm aware this is late, but I agree with AlexKuznetsov's suggestion to script the database, rather than going through the hassle of purging the data from the tables. If the TRUNCATE solution will not work, and you happen to have a large amount of data, issuing (logged) DELETE statements might take a long time, and you'll be left with identifiers that have not been reseeded (i.e. an INSERT statement into a table with an IDENTITY column would get you an ID of 50000 instead of an ID of 1).

To script a whole database, in SSMS, right-click the database, then select TASKS -> Generate scripts:

enter image description here

Click Next to skip the Wizard opening screen, and then select which objects you want to script:

enter image description here

In the Set scripting options screen, you can pick settings for the scripting, like whether to generate 1 script for all the objects, or separate scripts for the individual objects, and whether to save the file in Unicode or ANSI:

enter image description here

The wizard will show a summary, which you can use to verify everything is as desired, and close by clicking on 'Finish'.

Always answered 21/2, 2013 at 9:3 Comment(1)

Be careful, this way by default you will lost stuff like indexes if you don't go to "Advanced" button. – Jeb 19/12, 2019 at 18:39

First you'll have to disable all the triggers :

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

Run this script : (Taken from this post Thank you @SQLMenace)

SET NOCOUNT ON
GO

SELECT 'USE [' + db_name() +']';
;WITH a AS 
(
     SELECT 0 AS lvl, 
            t.object_id AS tblID 
     FROM sys.TABLES t
     WHERE t.is_ms_shipped = 0
       AND t.object_id NOT IN (SELECT f.referenced_object_id 
                               FROM sys.foreign_keys f)

     UNION ALL

     SELECT a.lvl + 1 AS lvl, 
            f.referenced_object_id AS tblId
     FROM a
     INNER JOIN sys.foreign_keys f ON a.tblId = f.parent_object_id 
                                   AND a.tblID <> f.referenced_object_id
)
SELECT 
    'Delete from ['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']' 
FROM a
GROUP BY tblId 
ORDER BY MAX(lvl),1

This script will produce DELETE statements in proper order. starting from referenced tables then referencing ones

Copy the DELETE FROM statements and run them once

enable triggers

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

Commit the changes :
```
begin transaction
commit;
```

Hage answered 16/9, 2014 at 10:20 Comment(1)

This does not work for me, the recursive query ends up in a loop. Perhaps because of self reverenses. – Familist 27/1, 2016 at 10:32

It is usually much faster to script out all the objects in the database, and create an empty one, that to delete from or truncate tables.

Evaevacuant answered 10/9, 2010 at 19:34 Comment(0)

Below a script that I used to remove all data from an SQL Server database

------------------------------------------------------------
/* Use database */ 
-------------------------------------------------------------

use somedatabase;

GO

------------------------------------------------------------------
/* Script to delete an repopulate the base [init database] */
------------------------------------------------------------------

-------------------------------------------------------------
/* Procedure delete all constraints */ 
-------------------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllConstraints
GO

CREATE PROCEDURE sp_DeleteAllConstraints
AS
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
GO

-----------------------------------------------------
/* Procedure delete all data from the database */ 
-----------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllData' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllData
GO

CREATE PROCEDURE sp_DeleteAllData
AS
    EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

-----------------------------------------------
/* Procedure enable all constraints */ 
-----------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_EnableAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_EnableAllConstraints
GO
-- ....
-- ....
-- ....

Franfranc answered 5/3, 2013 at 12:36 Comment(0)

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'

GO

Ostensive answered 1/2, 2016 at 13:12 Comment(0)

Save yourself some time/space and use TRUNCATE instead of DELETE when possible, will not bloat up your logfile in cases where you have a huge database.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable '
        IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
            DELETE FROM ?
        ELSE
            TRUNCATE TABLE ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

Bide answered 27/5, 2021 at 20:13 Comment(0)

EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sys.sp_msforeachtable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'

EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Sargassum answered 12/1, 2022 at 14:49 Comment(0)

As an alternative answer, if you Visual Studio SSDT or possibly Red Gate Sql Compare, you could simply run a schema comparison, script it out, drop the old database (possibly make a backup first in case there would be a reason that you will need that data), and then create a new database with the script created by the comparison tool. While on a very small database this may be more work, on a very large database it will be much quicker to simply drop the database then to deal with the different triggers and constraints that may be on the database.

Salic answered 22/5, 2019 at 18:40 Comment(0)

Combining all of the excellent tips in the existing answers along with a couple more, I've put together this script. It's a bit more comprehensive and should be way more efficient.

Before the delete step the script:

disables triggers (especially important if you have any on delete triggers)
disables constraint checks
disables non-clustered indexes (otherwise they get deleted row by row with the table)

For the delete step it will truncate (way faster) where possible or otherwise delete with a tablock hint (tablock will only help with heap tables and since we shrink the files anyway, it may not add anything here).

After the delete step the script will:

rebuild/enable all indexes (which are empty anyway)
enable constraint checks
enable triggers
re-seed identity columns
shrink the database files

Delete all data from a database:

use [your_database]
go

exec sp_MSforeachtable 'alter table ? disable trigger all'
go

exec sp_MSforeachtable 'alter table ? nocheck constraint all'
go

-- Disable enabled nonclustered indices
declare @script nvarchar(max)
declare cr cursor fast_forward read_only for
select 'alter index ' + quotename(i.name) + ' on ' + quotename(schema_name(t.schema_id))+'.'+ quotename(t.name) + ' disable'
from sys.indexes i inner join sys.tables t on i.object_id = t.object_id
where i.type_desc = 'nonclustered' and i.name is not null and i.is_disabled = 0;
open cr
fetch next from cr into @script
while @@fetch_status = 0
begin
    execute sp_executesql @script 
    fetch next from cr into @script
end
close cr
deallocate cr
go

exec sp_MSforeachtable 'set quoted_identifier on; if objectproperty(object_id(''?''), ''TableHasForeignRef'') = 1 delete from ? with (tablock) else truncate table ?'
go

exec sp_MSforeachtable 'set quoted_identifier on; alter index all on ? rebuild';
go

exec sp_MSforeachtable 'alter table ? with check check constraint all'
go

exec sp_MSforeachtable 'alter table ? enable trigger all'
go

-- Re-seed identity columns
exec sp_MSforeachtable 'if objectproperty(object_id(''?''), ''TableHasIdentity'') = 1 dbcc checkident(''?'', reseed, 0)'
go

-- Shrink the database files
declare @db_name nvarchar(200) = db_name()
dbcc shrinkdatabase (@db_name, 0);  
go

Equip answered 2/6, 2021 at 0:26 Comment(0)

/*
  Description: This script will remove all tables, views, functions, stored procedures and user defined types from a database.
*/
        declare @n char(1)
        set @n = char(10)

        declare @stmt nvarchar(max)

        -- procedures
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.procedures


        -- check constraints
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'
        from sys.check_constraints

        -- functions
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop function [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.objects
        where type in ( 'FN', 'IF', 'TF' )

        -- views
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop view [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.views

        -- foreign keys
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
        from sys.foreign_keys

        -- tables
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop table [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.tables

        -- user defined types
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop type [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.types
        where is_user_defined = 1


        exec sp_executesql @stmt

Ottilie answered 1/3 at 6:14 Comment(2)

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Searby 2/3 at 0:27

Take the tour, visit the help center and learn how to use markdown. – Camfort 2/3 at 9:47

-1

Yes, it is possible to delete with a single line of code

SELECT 'TRUNCATE TABLE ' + d.NAME + ';' 
FROM   sys.tables d 
WHERE  type = 'U'

Berylberyle answered 17/10, 2018 at 7:8 Comment(3)

This gives me a new table with a truncate statement for each table. It does not actually delete anything, and unfortunately does it take care of the problem of dropping constraints first. Too bad, I was hoping for an answer like that, without the use of sp_MSForEachTable (which doesn't exist for me, Azure SQL Server)! – Handball 30/10, 2018 at 13:36

yes. true. its create truncate script for all tables. Use that script to delete tables data. – Berylberyle 13/2, 2019 at 9:7

This solution only works in the event that there aren't any relationships, as it has no way of guaranteeing that the tables are dropped in the correct order. Also, if there are any triggers on deletions of data this could pose unintended consequences. – Salic 22/5, 2019 at 18:42

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags