brianwells05
asked on
Loop through tables in SQL Server and delete rows
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
exec sp_msforeachtable 'DELETE ?'
Just execute it as many times as necessary until you do not get any error message for foreign key violation.
ASKER
Several good answers. I agree with scripting the database, but I needed the delete option in my specific case. Thanks for the input!
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_2667-The-Difference-Between-Truncation-and-Deletion.html