Delete data from all tables in database

Posted on 2006-04-10
Last Modified: 2009-12-16
I need to delete data from all 68 of my tables in my sql 2000 database.  I am done developing and ready for production use.  I can use a select statement to list all of the tables, but not sure how to delete the data from them through a script.
Question by:williak7
    LVL 14

    Accepted Solution

    Select 'Delete ' + Name from sysobjects where xtype = 'U'
    or better yet
    Select 'truncate table ' + Name from sysobjects where xtype = 'U'

    Then copy and past the results into query analyzer, and execute
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    If ypu need to delete all the data, it is better to recreate the database / tables again
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    try this too

    exec sp_msForEachTable @command1 = "truncate table ? print 'truncating ?'"

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now