Clear database - delete all records from a database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rafal S. » Clear database - delete all records from a...

Clear database - delete all records from a database

Rate Topic

Display Mode

Topic Options

Author

Message

skra

Posted Saturday, February 02, 2008 3:35 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 711, Visits: 212

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

Post #450857

ronmoses

Posted Monday, April 14, 2008 5:56 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 661, Visits: 698

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

Post #484322

kwame poku

Posted Monday, April 14, 2008 10:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 11, 2013 2:58 PM
Points: 5, Visits: 195

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

Post #484530

Robert Davis

Posted Monday, April 14, 2008 11:46 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 1,559, Visits: 1,404

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
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring

Post #484574

skra

Posted Monday, April 14, 2008 3:05 PM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 711, Visits: 212

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.

Post #484657

skra

Posted Monday, April 14, 2008 3:08 PM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 711, Visits: 212

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.

Post #484658

skra

Posted Wednesday, April 16, 2008 1:27 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 711, Visits: 212

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.

Post #485454

tenga

Posted Monday, February 16, 2009 5:53 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, July 09, 2009 4:57 AM
Points: 58, Visits: 32

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.

Post #657621

« Prev Topic | Next Topic »

Permissions