Click here to Skip to main content
14,545,210 members
Rate this:
Please Sign up or sign in to 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
0x01AA 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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   
Comments
0x01AA 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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100