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.