Deleting all Rows in all Tables

Category : Blogs Published : August 9, 2013 User Rating : 4 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




There may be times when you need to clear down all the existing data in a database. However if you try and delete the data in each table in turn, then the chances are that sooner or later you will encounter a failure due to a constraint. You’ll probably see an error like this one :
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeader_Address_ShipToAddressID". The conflict occurred in database "AdventureWorks2008R2", table "Sales.SalesOrderHeader", column 'ShipToAddressID'.

The statement has been terminated.
The problem here is that the parent table contents are being deleted before the child table. While you could potentially get around this by changing the order that table contents are deleted (assuming you are using DELETE rather than TRUNCATE), it can be difficult to get this order correct and in any case if there is a circular constraint reference this just won’t work.

An easier approach is to do the following :
  • Disable constraints on each table
  • Delete data in each table
  • Re-enable constraints on each table
Once the constraints on each table have been disabled it doesn’t matter in which order the data is deleted.

The following statements will create the SQL that will do each of these steps. Just paste the results from each query back into another query window and then run them.

-- Create SQL to disable constraints on each table
SELECT      'ALTER TABLE [' + S.name + '].[' + T.name + '] NOCHECK CONSTRAINT ALL'
                AS [SQL to run:]
FROM        sys.tables T
JOIN        sys.schemas S
ON          T.schema_id = S.schema_id
ORDER BY    T.name


-- Create SQL to delete data in each table
SELECT      'DELETE FROM [' + S.name + '].[' + T.name + ']'
                AS [SQL to run:]
FROM        sys.tables T
JOIN        sys.schemas S
ON          T.schema_id = S.schema_id
ORDER BY    T.name


-- Create SQL to re-enable constraints on each table
SELECT      'ALTER TABLE [' + S.name + '].[' + T.name + '] WITH CHECK CHECK CONSTRAINT ALL'
                AS [SQL to run:]
FROM        sys.tables T
JOIN        sys.schemas S
ON          T.schema_id = S.schema_id
ORDER BY    T.name

Other Issues

You may have noticed that I mentioned using DELETE rather than TRUNCATE. This is because TRUNCATE will fail even if even if the constraints are disabled and/or there is no data in the tables.

If you have triggers on tables you may find that you need to disable them if they prevent you from deleting data.
Link back to this article : http://www.sqlmatters.com/Articles/Deleting all Rows in all Tables.aspx

Keywords

SQL, scripts


Comments

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered