How to delete all the data of a database while keep the table structure?

Question

Now I have a database with multiple tables and foreign key constraints. Need to clear all the data but keep the table structure.

Tried with truncate, but receive this error

Cannot truncate table 'demo1' because it is being referenced by a FOREIGN KEY constraint.

Here is the code:

Alter table demo1 NOCHECK CONSTRAINT ALL
TRUNCATE TABLE demo1

Answer 1

Hi @Lylyy

TRUNCATE TABLE is a DDL command, it cannot check to see whether the records in the table are being referenced by a record in the child table.

Therefore, you need DELETE here, because the database is able to make sure that it isn't being referenced by another record.

How to delete all the data of a database while keep the table structure?

Solution 1:

Execute this script

SELECT 'DELETE ' + quotename(S.name) + '.' + quotename(T.name) + char(13) + char(10) + 'GO' + char(13) + char(10) 
FROM sys.tables T JOIN sys.schemas S ON T.schema_id = S.schema_id

Then copy the query result to a new query window and execute(F5) until no error message received.

Solution 2:

• Create a table variable to store the constraint drop and creation scripts for the database.
• Load the data for all tables in the database.
• Execute a cursor to drop all constraints.
• Truncate all tables.
• Recreate all the constraints.

Please refer to this article for detailed scripts.

Best regards,

Cosmog Hong

Answer 2

Easier way: Generate a CREATE script for the database with all objects and use it to create an empty database in same structur.

See Generate Scripts (SQL Server Management Studio)