[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Loop through tables in SQL Server and delete rows

Posted on 2011-09-26
5
Medium Priority
?
226 Views
Last Modified: 2012-06-27
Does anyone know how to loop through all tables in a SQL Server database and execute a DELETE FROM... on each table? I want to clean the database completely and thought there may be a way to utilize a loop for this. Thanks for any insight!
0
Comment
Question by:brianwells05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 2000 total points
ID: 36600443
If you have foreign keys, your loop will fail. You better script your database, drop it, and recreate it from your script
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 36600446
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36600850
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36671472
If you like to live dangerously you can try something like this:
exec sp_msforeachtable 'DELETE ?'

Just execute it as many times as necessary until you do not get any error message for foreign key violation.
0
 

Author Closing Comment

by:brianwells05
ID: 36719229
Several good answers. I agree with scripting the database, but I needed the delete option in my specific case. Thanks for the input!
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

649 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