Delete all rows from all tables

Have you ever come across the problem to delete all rows from all tables available in a database? One of my colleague asked me this question that how can we delete all rows from all tables of a database. So, in this post, we are going to discuss, how we can delete all rows from all tables of a SQL Server database.

To delete all rows from a table named ProductCategory, we can simply use “DELETE FROM ProductCategory” command. But in case the table is being referred by a foreign key constraints from some tables and / or fires a trigger to insert few rows in an another table in the database, we must have to delete all related rows from child tables before we start deleting parent table rows. We also need to delete records from those table which are getting inserted rows as a result of a trigger execution. Let me describe these challenges in more detail below:

1. ProductCategory table is being referred by ProductSubCategory table with foreign key and ProductSubCategory table is being referred by Product table with a foreign key, to delete all rows from ProductCategory table, first we have to delete all referenced rows from Product table then from ProductSubCategory table, then we can perform delete operation on ProductCategory table. We cannot delete the parent table record until they are being referred in any child table.
2. Table ProductCategory has a FOR DELETE trigger to insert the deleted records in ProductCategory _History table (to maintain history of the table), we need to perform delete operation on these tables accordingly.

To avoid the above discussed challenges, we can follow below steps to delete all rows from all tables:

We can achieve our goal in various ways, and few of them are here:

Approach 1: Using dynamic SQL statement from master table sys.tables

--Declare a global variable used throught the query
DECLARE @SQLStmt NVARCHAR(MAX) = N''
--Disable all check constraints on all tables
SELECT @SQLStmt = @SQLStmt + COALESCE('ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.' + name + ' NOCHECK CONSTRAINT ALL; ', '')
FROM sys.tables
SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt
EXEC (@SQLStmt)
SET @SQLStmt = N''

--Disable all triggers on all tables
SELECT @SQLStmt = @SQLStmt + COALESCE(N'DISABLE TRIGGER ALL ON ' + SCHEMA_NAME(schema_id) + '.' + name + '; ', '')
FROM sys.tables
SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt
EXEC (@SQLStmt)
SET @SQLStmt = N''

--Delete all data from all tables
SELECT @SQLStmt = @SQLStmt + COALESCE('DELETE FROM ' + SCHEMA_NAME(schema_id) + '.' + name + '; ', '')
FROM sys.tables
SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt
EXEC (@SQLStmt)
SET @SQLStmt = N''

--Enable all constraints on all tables
SELECT @SQLStmt = @SQLStmt + COALESCE('ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.' + name + ' CHECK CONSTRAINT ALL; ', '')
FROM sys.tables
SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt
EXEC (@SQLStmt)
SET @SQLStmt = N''

--Enable all triggers on all tables
SELECT @SQLStmt = @SQLStmt + COALESCE(N'ENABLE TRIGGER ALL ON ' + SCHEMA_NAME(schema_id) + '.' + name + '; ', '')
FROM sys.tables
SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt
EXEC (@SQLStmt)

Identity Reseed

In case you want to reseed the values of identity columns of tables to 1, execute below script:

--Ressed the identity columns seed value
DECLARE @SQLStmt NVARCHAR(MAX) = N''
SELECT
@SQLStmt = @SQLStmt + COALESCE('DBCC CHECKIDENT(' + CHAR(39) + SCHEMA_NAME(schema_id) + '.' + st.name + CHAR(39) + ', RESEED, 0)', '')
FROM sys.columns sc
INNER JOIN sys.tables st ON sc.object_id = st.object_id and sc.is_identity = 1
SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt
EXEC (@SQLStmt)

Approach 2: Using undocumented procedure sp_MSForEachTable

--Disable all check constraints on all tables
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

--Disable all triggers on all tables
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO

--Delete all data from all tables
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
GO

--Enable all constraints on all tables
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

--Enable all triggers on all tables
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Identity Reseed

In case you want to reseed the values of identity columns of tables to 1, execute below script:

--Ressed the identity columns seed value
EXEC sp_MSForEachTable 'IF(OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1) DBCC CHECKIDENT(''?'', RESEED, 0)'
GO

Note: Before executing DELETE command with stored procedure “sp_MSForEachTable”, I have used “SET QUOTED_IDENTIFIER ON;” to avoid an error “DELETE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER'”. Also remember that, this stored procedure is not documented on MSDN.

Conclusion:

We can achieve this task in various ways and few of them I have shared above. You can try with your method and in case you like to share, kindly post your script in comment section. Don’t forget to rate and share this post. Thanks for your reading.