May 18, 2007 at 7:07 am
Hi all,
how can I empty an entire database (SS2005) without to have to write many Delete statement?
Thanks a lot.
Luigi
May 18, 2007 at 8:35 am
Quick and dirty, completely inelegant and unefficient and you might have to run it two or three times in a row, but it will do the job.
sp_msforeachtable
'delete from ?'
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 18, 2007 at 8:45 am
Thank you so much Grant
Luigi
May 19, 2007 at 4:17 pm
You may get into issues if your tables are bigger while deleting the data...
sp_msforeachtable 'truncate table ?'
Note: if your table have FK then drop the FKs first and recreate them after truncate...
MohammedU
Microsoft SQL Server MVP
May 22, 2007 at 5:54 am
if you have tables with large number of records it may be a good idea to just script out all your creates and recreate the db.
May 22, 2007 at 6:02 am
Actually, I haddn't thought of that, but yeah, in most cases this is probably quicker.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 22, 2007 at 6:04 am
Ok, thanks you all.
Luigi
May 22, 2007 at 1:17 pm
sounds like what you really want is DROP DATABASE.
---------------------------------------
elsasoft.org
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply