Post reply

Clear database - delete all records from a database

  • February 2, 2008 at 3:35 am

    #181619

    Comments posted to this topic are about the item Clear database - delete all records from a database

  • April 14, 2008 at 5:56 am

    #801992

    Good script! This would be perfect for me if it included one additional item: Re-initializing all identity fields to 1. I can do it table-by-table but if there's a way to do it globally, even better.

    Ron Moses

    ConEst Software Systems

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • April 14, 2008 at 10:42 am

    #802169

    Good Script.

    My question is I have some reference tables in my Database. All other databases can be cleared with the exception of the reference tables.

    Any help on that.

    Thank you.

    KP

  • April 14, 2008 at 11:46 am

    #802209

    Unless the database is very small, this script will blow up the tran log.

    Things I would change or add to this script:

    1. Abort the script if the specified/current database is mirrored or replicated.

    2. Abort the script if a user tried to run it on a system database.

    3. Use Truncate Table instead of Delete From for improved performance, to prevent tran log bloat, and to reset identity values.

    4. Get rid of cursors (not needed).

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • April 14, 2008 at 3:05 pm

    #802278

    I have already tried to use 'truncate table' but it raises an error if there are any foreign key constraints. I would have to drop them before truncating table unless I find some way to disable and enable them after cleaning the database.

    By the way this script will not work if relationships are guarded by triggers (some old database may still use this solution).

    And You are right: I should not have used cursors.

  • April 14, 2008 at 3:08 pm

    #802279

    And good point about transaction logs. Mayby the script should check the recovery mode and set it to simple before cleaning the db and restore settings after the work is finished.

  • April 16, 2008 at 1:27 am

    #802915

    I have just submitted a new version of the procedure:

    - without cursors

    - with reseeding identities

    - with changing (and restoring) recovery mode

    Now it is waiting for approval.

  • February 16, 2009 at 5:53 am

    #944445

    Actually i m new to this, so i had a problem understanding this code. it would be really helpfull if u could help me getting it more clear. Thanks anyways.

  • May 11, 2016 at 11:41 am

    #1878337

    Thanks for the script.

Viewing 9 posts - 1 through 8 (of 8 total)

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