Delete all data in SQL Server database

SqlSql ServerDelete RowMulti Table-Delete

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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

See also: Delete all data in database (when you have FKs)

Solution 3 - Sql

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

Solution 4 - Sql

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'.

Solution 5 - Sql

  1. First you'll have to disable all the triggers :

     sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

  2. 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

  1. Copy the DELETE FROM statements and run them once

  2. enable triggers

     sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

  3. Commit the changes :

     begin transaction
 commit;

Solution 6 - Sql

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.

Solution 7 - Sql

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

Solution 8 - Sql

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

Solution 9 - Sql

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.

Solution 10 - Sql

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

Solution 11 - Sql

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

Solution 12 - Sql

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'

Solution 13 - Sql

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

SELECT 'TRUNCATE TABLE ' + d.NAME + ';' 
FROM   sys.tables d 
WHERE  type = 'U'
Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Delete Row Solutions

Previous Solution:

What's the dSYM and how to use it? (iOS SDK)

DebuggingSdkCompilationIos

Next Solution:

What is the difference between “int” and “uint” / “long” and “ulong”?

C#TypesIntegerUnsignedSigned

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAndreyAkinshinView Question on Stackoverflow
Solution 1 - SqlRyan KirkmanView Answer on Stackoverflow
Solution 2 - SqlSQLMenaceView Answer on Stackoverflow
Solution 3 - SqlHarpalView Answer on Stackoverflow
Solution 4 - SqlSchmitzITView Answer on Stackoverflow
Solution 5 - SqlmounaimView Answer on Stackoverflow
Solution 6 - SqlA-KView Answer on Stackoverflow
Solution 7 - SqlbenahmView Answer on Stackoverflow
Solution 8 - SqlmikemView Answer on Stackoverflow
Solution 9 - Sqldmoore1181View Answer on Stackoverflow
Solution 10 - Sqlsbarclay8308View Answer on Stackoverflow
Solution 11 - SqlAndyMcView Answer on Stackoverflow
Solution 12 - SqlToqeer YousafView Answer on Stackoverflow
Solution 13 - SqlBuddhika De SilvaView Answer on Stackoverflow