Clear database - delete all records from a database

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

  • 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

  • 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.


  • 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]

  • 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.

  • 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.

  • 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.

  • 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.

  • 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