Delete all data in the database

5 pts.
Tags:
SQL
Good day! Currently I am using MS SQL Server 2000. I wish to delete all data in the database table except data in login table and security table. Furthermore, TRUNCATE is done on stand-alone and child tables, else DELETE if table has any foreign key references (normally refer to parent tables): Is there any suggested script/stored procedure to automate the process? Thanks a million!

Answer Wiki

Thanks. We'll let you know when a new response is added.

One thing you can do is:

1.Back up your database
2. Make a script to create all table references.
3. Make a script to drop all table references.
4. Make a script to truncate all tables, except the ones you want to keep

Restore your backup in a new database, and use this new database for testing your scripts.

First drop all table references, second run the truncate script and third restore al the table references and probe your database integrity

Hope it helps you

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Brijesh
    Hi,

    Hope the below resource guides you with sufficient information Delete all data in database and check this out 
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
    GO 
    
    EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
    GO 
    
     
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
    GO
    10,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following