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

Solution 1:

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:

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:

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:

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:

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"