• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2164
  • Last Modified:

Delete data from all tables in database

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.
0
williak7
Asked:
williak7
  • 2
1 Solution
 
adwisemanCommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
If ypu need to delete all the data, it is better to recreate the database / tables again
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this too

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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now