T-SQL script to purge all the tables including foreign key references

  • Brahmanand Shukla

    SSChasing Mays

    Points: 660

    Comments posted to this topic are about the item T-SQL script to purge all the tables including foreign key references

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21010

    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

  • Brahmanand Shukla

    SSChasing Mays

    Points: 660

    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.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply