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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy