?
Solved

Loop through tables in SQL Server and delete rows

Posted on 2011-09-26
5
Medium Priority
?
228 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
5 Comments
 
LVL 71

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 71

Accepted Solution

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

592 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