How to delete all rows from all tables in a SQL Server database?

Sql Server-2005

Sql Server-2005 Problem Overview

How to delete all rows from all tables in a SQL Server database?

Sql Server-2005 Solutions

Solution 1 - Sql Server-2005

Note that TRUNCATE won't work if you have any referential integrity set.

In that case, this will work:

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

Edit: To be clear, the ? in the statements is a ?. It's replaced with the table name by the sp_MSForEachTable procedure.

Solution 2 - Sql Server-2005

In my recent project my task was to clean an entire database by using sql statement and each table having many constraints like Primary Key and Foreign Key. There are more than 1000 tables in database so its not possible to write a delete query on each and ever table.

By using a stored procedure named sp_MSForEachTable which allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or a different T-SQL commands against every table in the database.

So follow the below steps to truncate all tables in a SQL Server Database:

Step 1- Disable all constraints on the database by using below sql query :

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

Step 2- Execute a Delete or truncate operation on each table of the database by using below sql command :

EXEC sys.sp_msforeachtable 'DELETE FROM ?'

Step 3- Enable all constraints on the database by using below sql statement:

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

Solution 3 - Sql Server-2005

I had to delete all the rows and did it with the next script:

DECLARE @Nombre NVARCHAR(MAX);
DECLARE curso CURSOR FAST_FORWARD 
FOR 
Select Object_name(object_id) AS Nombre from sys.objects where type = 'U'

OPEN curso
FETCH NEXT FROM curso INTO @Nombre

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
DECLARE @statement NVARCHAR(200);
SET @statement = 'DELETE FROM ' + @Nombre;
print @statement
execute sp_executesql @statement;
END
FETCH NEXT FROM curso INTO @Nombre
END
CLOSE curso
DEALLOCATE curso

Hope this helps!

Solution 4 - Sql Server-2005

In my case, I needed to set QUOTED_IDENTIFIER on. This led to a slight modification of Mark Rendle's answer above:

EXEC sp_MSforeachtable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
GO
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSforeachtable 'ENABLE TRIGGER ALL ON ?'
GO

Solution 5 - Sql Server-2005

Here is a solution that:

  1. Drops constraints (thanks to this post)
  2. Iterates through INFORMATION_SCHEMA.TABLES for a particular database
  3. SELECTS tables based on some search criteria
  4. Deletes all the data from those tables
  5. Re-adds constraints
  6. Allows for ignoring of certain tables such as sysdiagrams and __RefactorLog

I initially tried EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?', but that deleted my diagrams.

USE <DB name>;
GO

-- Disable all constraints in the database
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

declare @catalog nvarchar(250);
declare @schema nvarchar(250);
declare @tbl nvarchar(250);
DECLARE i CURSOR LOCAL FAST_FORWARD FOR select
                                        TABLE_CATALOG,
                                        TABLE_SCHEMA,
                                        TABLE_NAME
                                        from INFORMATION_SCHEMA.TABLES
                                        where
                                        TABLE_TYPE = 'BASE TABLE'
                                        AND TABLE_NAME != 'sysdiagrams'
                                        AND TABLE_NAME != '__RefactorLog'

OPEN i;
FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM [' + @catalog + '].[' + @schema + '].[' + @tbl + '];'
        /* Make sure these are the commands you want to execute before executing */
        PRINT 'Executing statement: ' + @sql
        -- EXECUTE sp_executesql @sql
        FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
    END
CLOSE i;
DEALLOCATE i;

-- Re-enable all constraints again
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Solution 6 - Sql Server-2005

Set nocount on

Exec sp_MSforeachtable 'Alter Table ? NoCheck Constraint All'

Exec sp_MSforeachtable
'
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1
Begin
-- Just to know what all table used delete syntax.
Print ''Delete from '' + ''?''
Delete From ?
End
Else
Begin
-- Just to know what all table used Truncate syntax.
Print ''Truncate Table '' + ''?''
Truncate Table ?
End
'

Exec sp_MSforeachtable 'Alter Table ? Check Constraint All'

Solution 7 - Sql Server-2005

You could delete all the rows from all tables using an approach like Rubens suggested, or you could just drop and recreate all the tables. Always a good idea to have the full db creation scripts anyway so that may be the easiest/quickest method.

Solution 8 - Sql Server-2005

If you just have constraints just paste this lines in the query and run it

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

Solution 9 - Sql Server-2005

For some requirements we might have to skip certain tables. I wrote the below script to add some extra conditions to filter the list of tables. The below script will also display the pre delete count and post delete count.

		IF OBJECT_ID('TEMPDB..#TEMPRECORDCOUNT') IS NOT NULL 
		DROP TABLE #TEMPRECORDCOUNT 

		CREATE TABLE #TEMPRECORDCOUNT 
			(	 TABLENAME NVARCHAR(128)
				,PREDELETECOUNT BIGINT
				,POSTDELETECOUNT BIGINT
			) 

		INSERT INTO #TEMPRECORDCOUNT (TABLENAME, PREDELETECOUNT, POSTDELETECOUNT)

		SELECT   O.name TableName
				,DDPS.ROW_COUNT PREDELETECOUNT
				,NULL  FROM sys.objects O 
				
		INNER JOIN (

					SELECT OBJECT_ID, SUM(row_count) ROW_COUNT 
					FROM SYS.DM_DB_PARTITION_STATS
					GROUP BY OBJECT_ID
				   ) DDPS ON DDPS.OBJECT_ID = O.OBJECT_ID
		WHERE O.type = 'U' AND O.name NOT LIKE 'OC%' AND O.schema_id = 1

		DECLARE @TableName NVARCHAR(MAX);
		DECLARE TableDeleteCursor CURSOR FAST_FORWARD 
		FOR 
		SELECT TableName from #TEMPRECORDCOUNT

		OPEN TableDeleteCursor
		FETCH NEXT FROM TableDeleteCursor INTO @TableName

		WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
		BEGIN
		DECLARE @STATEMENT NVARCHAR(MAX);
		SET @STATEMENT = ' DISABLE TRIGGER ALL ON ' + @TableName + 
						 '; ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ALL' +
						 '; DELETE FROM ' + @TableName +
						 '; ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ALL' +
						 '; ENABLE TRIGGER ALL ON ' + @TableName;
		PRINT @STATEMENT
		EXECUTE SP_EXECUTESQL @STATEMENT;
		END
		FETCH NEXT FROM TableDeleteCursor INTO @TableName
		END
		CLOSE TableDeleteCursor
		DEALLOCATE TableDeleteCursor

		UPDATE T 
		 SET T.POSTDELETECOUNT = I.ROW_COUNT 
		 FROM #TEMPRECORDCOUNT T 
		 INNER JOIN (
						SELECT O.name TableName, DDPS.ROW_COUNT ROW_COUNT  
						FROM sys.objects O 
						INNER JOIN (

								SELECT OBJECT_ID, SUM(row_count) ROW_COUNT 
								FROM SYS.DM_DB_PARTITION_STATS
								GROUP BY OBJECT_ID
							   ) DDPS ON DDPS.OBJECT_ID = O.OBJECT_ID
						WHERE O.type = 'U' AND O.name NOT LIKE 'OC%' AND O.schema_id = 1

					) I ON I.TableName COLLATE DATABASE_DEFAULT = T.TABLENAME 

		SELECT * FROM #TEMPRECORDCOUNT 
		ORDER BY TABLENAME ASC

Solution 10 - Sql Server-2005

This answer builds on Zach Smith's answer by resetting the identity column as well:

  1. Disabling all constraints
  2. Iterating through all tables except those you choose to exclude
  3. Deletes all rows from the table
  4. Resets the identity column if one exists
  5. Re-enables all constraints

Here is the query:

-- Disable all constraints in the database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

declare @catalog nvarchar(250);
declare @schema nvarchar(250);
declare @tbl nvarchar(250);
DECLARE i CURSOR LOCAL FAST_FORWARD FOR select
                                        TABLE_CATALOG,
                                        TABLE_SCHEMA,
                                        TABLE_NAME
                                        from INFORMATION_SCHEMA.TABLES
                                        where
                                        TABLE_TYPE = 'BASE TABLE'
                                        AND TABLE_NAME != 'sysdiagrams'
                                        AND TABLE_NAME != '__RefactorLog'
										-- Optional
                                        -- AND (TABLE_SCHEMA = 'dbo')

OPEN i;
FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM [' + @catalog + '].[' + @schema + '].[' + @tbl + '];'
        /* Make sure these are the commands you want to execute before executing */
        PRINT 'Executing statement: ' + @sql
        --EXECUTE sp_executesql @sql

		-- Reset identity counter if one exists
		IF ((SELECT OBJECTPROPERTY( OBJECT_ID(@catalog + '.' + @schema + '.' + @tbl), 'TableHasIdentity')) = 1)
		BEGIN
			SET @sql = N'DBCC CHECKIDENT ([' + @catalog + '.' + @schema + '.' + @tbl + '], RESEED, 0)'
			PRINT 'Executing statement: ' + @sql
			--EXECUTE sp_executesql @sql
		END		

        FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
    END
CLOSE i;
DEALLOCATE i;

-- Re-enable all constraints again
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Solution 11 - Sql Server-2005

--Load tables to delete from
SELECT 
DISTINCT
' Delete top 1000000 from <DBName>.<schema>.' + c.TABLE_NAME + ' WHERE <Filter Clause Here>' AS query,c.TABLE_NAME AS TableName, IsDeleted=0, '<InsertSomeDescriptorHere>' AS [Source]--,t.TABLE_TYPE, c.*
			INTO dbo.AllTablesToDeleteFrom
			FROM INFORMATION_SCHEMA.TABLES AS t
			INNER JOIN information_schema.columns c ON c.TABLE_NAME = t.TABLE_NAME 
	WHERE c.COLUMN_NAME = '<column name>'
		   AND c.TABLE_SCHEMA = 'dbo'
		   AND c.TABLE_CATALOG = '<DB Name here>'
		   AND t.TABLE_TYPE='Base table'
		   --AND t.TABLE_NAME LIKE '<put filter here>'

			DECLARE @TableSelect NVARCHAR(1000)= '';
			DECLARE @Table NVARCHAR(1000)= '';
			DECLARE @IsDeleted INT= 0;
			DECLARE @NumRows INT = 1000000;
			DECLARE @Source NVARCHAR(50)='';


			WHILE ( @IsDeleted = 0 )
				BEGIN
				--This grabs one table at a time to be deleted from. @TableSelect has the sql to execute. it is important to order by IsDeleted ASC
				--because it will pull tables to delete from by those that have a 0=IsDeleted first. Once the loop grabs a table with IsDeleted=1 then this will pop out of loop

					SELECT TOP 1
							@TableSelect = query,
							@IsDeleted = IsDeleted,
							@Table = TableName,
							@Source=[a].[Source]
					FROM    dbo.AllTablesToDeleteFrom a
					WHERE a.[Source]='SomeDescriptorHere'--use only if needed
					ORDER BY a.IsDeleted ASC;--this is required because only those records returned with IsDeleted=0 will run through loop

					--SELECT  @Table; can add this in to  monitor what table is being deleted from

					WHILE ( @NumRows = 1000000 )--only delete a million rows at a time?
	
    				BEGIN 
					EXEC sp_executesql @TableSelect;
					SET @NumRows = @@ROWCOUNT;
					--IF @NumRows = 1000000 --can do something here if needed
					--One wants this loop to continue as long as a million rows is deleted. Once < 1 million rows is deleted it pops out of loop
					--and grabs next table to delete
					--    BEGIN
					--SELECT  @NumRows;--can add this in to see current number of deleted records for table
							INSERT  INTO dbo.DeleteFromAllTables
									( tableName,
									  query,
									  cnt,
									  [Source]
									)
							SELECT  @Table,
									@TableSelect,
									@NumRows,
									@Source;
					 --   END; 
				END; 

		

SET @NumRows = 1000000;

UPDATE  a
SET     a.IsDeleted = 1
FROM    dbo.AllTablesToDeleteFrom a
WHERE   a.TableName = @Table;
--flag this as deleted so you can move on to the next table to delete from

END;

Solution 12 - Sql Server-2005

if you want to delete the whole table, you must follow the next SQL instruction

Delete  FROM TABLE Where PRIMARY_KEY_ is Not NULL;
Categories
Recommended Sql Server-2005 Solutions

Previous Solution:

What is the difference between lemmatization vs stemming?

NlpNltkLemmatization

Next Solution:

Why does javascript replace only first instance when using replace?

JavascriptJquery

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
Questionsurajit khamrai View Question on Stackoverflow
Solution 1 - Sql Server-2005Mark RendleView Answer on Stackoverflow
Solution 2 - Sql Server-2005e-techpulseView Answer on Stackoverflow
Solution 3 - Sql Server-2005Gonza OviedoView Answer on Stackoverflow
Solution 4 - Sql Server-2005William JockuschView Answer on Stackoverflow
Solution 5 - Sql Server-2005Zach SmithView Answer on Stackoverflow
Solution 6 - Sql Server-2005yadavrView Answer on Stackoverflow
Solution 7 - Sql Server-2005AdaTheDevView Answer on Stackoverflow
Solution 8 - Sql Server-2005Helder PereiraView Answer on Stackoverflow
Solution 9 - Sql Server-2005Balasubramanian SView Answer on Stackoverflow
Solution 10 - Sql Server-2005SeafishView Answer on Stackoverflow
Solution 11 - Sql Server-2005jpsView Answer on Stackoverflow
Solution 12 - Sql Server-2005malejView Answer on Stackoverflow