Menu
I use SQL server 2008 R2.
Is there a SQL command to empty the database, instead of having to truncate all 20 my tables?
I just want to delete the data not the structure.
640
No, you can only truncate a single table with TRUNCATE command.
The SQL TRUNCATE TABLE command is used to delete complete data from an existing table. You can also use DROP TABLE command to delete complete table but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.
You cannot truncate a table that has foreign key constraints. I would script your truncate statement to drop the constraints, then truncate the table, and then re-create the constraints.
Select Database -> More Database Operations -> Truncate Database (Shift+Del) to truncate (make the tables empty) all the tables of a database (however, the Tables and other objects (Stored Procedures, Views etc.) are not dropped).
You can use the sp_MSforeachtable stored procedure like so:
USE MyDatabase EXEC sp_MSforeachtable 'TRUNCATE TABLE ?' Be warned that this will delete (by truncation) ALL data from all user tables. And in case you can't TRUNCATE due to foreign keys etc. you can run the same as a delete:
USE MyDatabase EXEC sp_MSforeachtable 'DELETE FROM ?' I use this script
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’ EXEC sp_MSForEachTable ‘DELETE FROM ?’ EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’ GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
