Click here to Skip to main content
15,921,226 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Need command
 
Clear all data from all tables in database and reset id for all tables in same table ms sql server

Thanks


What I have tried:

<pre>Need command
 
Clear all data from all tables in database and reset id for all tables in same table ms sql server

Thanks
Posted
Updated 16-May-20 6:43am
Comments
[no name] 16-May-20 10:12am    
Instead of delete, maybe it is easier to script the current db and recreate it? Something like this: SQL Server 2012 copy database without data - Stack Overflow[^]
F-ES Sitecore 16-May-20 10:38am    
Use TRUNCATE to remove data and reset the ID. If you have relations set up then you'll need to do it in the right order.
phil.o 16-May-20 10:48am    
You should make it an answer.

1 solution

May be below post might help

Link

Copying as-is from the above link

Note that TRUNCATE won't work if you have any referential integrity set.

In that case, this will work:

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO
 
Share this answer
 
Comments
[no name] 16-May-20 14:45pm    
Makes sense, +5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900