A neat little script that fits in well with some of the themes we're seeing recently about having clean databases with no customer data inside.
there are 2 other ways I can see to do this that might be quicker
1 - script all of your FK objects and save them somewhere else , drop the FKs and then run sp_msforeachtable 'truncate table ?'
(yes I know sp_msforeachtable is unsupported) - then re-add your foreign keys - allows you to use truncate everywhere
2-just grab a script of the database (sql compare can do this nicely), run the create script and you will have a nice empty database - then just rename the old one and put the new one back in place - one benefit of this is that you won't get a big log file
i'd also add - if you are truncating in some tables and deleting in others... if you have identity columns then you might want to add a step to reseed the identity
and since your database is clean, you could issue a checkpoint and clear down the transaction log - depends on how you use that database
Thanks Mike for sharing your valuable feedback ! I agree with both of your feedback - Reset Identity and Checkpoint.
Will soon publish the revised version of the script with your feedback incorporated in it.
Comments posted to this topic are about the item T-SQL script to purge all the tables including foreign key references
Viewing 3 posts - 1 through 2 (of 2 total)