November 1, 2019 at 3:28 pm
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
MVDBA
November 4, 2019 at 4:13 am
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.
December 10, 2019 at 3:57 pm
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)
You must be logged in to reply to this topic. Login to reply