Delete all rows from all tables 2


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:

Delete all rows from all tables

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.

Rate This
[Total: 10    Average: 3.3/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a comment

Your email address will not be published. Required fields are marked *

*

2 thoughts on “Delete all rows from all tables

  • Michael storey

    dont use delete. Use truncate as the individual transactions aren’t logged in the tran log and won’t cause unnecessary and possible fatal consequences.

    Pretty sure there is a lot of sense in simply dropping and recreating the tables (and dependencies) too.

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan Post author

      Hi Michael, I agree with you that truncate is less resource intensive than delete but we cannot use a truncate statement on a table being referred with foreign keys even disabled. To execute truncate instead of delete, we need to follow these steps:

      1) Extract the definitions of all foreign keys from the database for drop and create. 2) Drop these constraints using drop script, 3) Truncate the tables. 4) Recreate the foreign keys constraints.

      If you are good to play with the definitions of the objects, you can use truncate.