Solved

Loop through tables in SQL Server and delete rows

Posted on 2011-09-26
5
218 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 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 500 total points
Comment Utility
If you have foreign keys, your loop will fail. You better script your database, drop it, and recreate it from your script
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
Comment Utility
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

13 Experts available now in Live!

Get 1:1 Help Now