Solved

Loop through tables in SQL Server and delete rows

Posted on 2011-09-26
5
223 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 500 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

705 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