Delete all records from all tables - conditionally

Posted on 2014-12-17
Last Modified: 2014-12-22

In my SQL Server database I have numerous tables.
Some tables (NOT ALL) have a boolean (bit) field called "IsVerwijderd".

I want to delete all records from all tables inside the database where field "IsVerwijderd" is present and is set to TRUE.

Does anybody have a solution for this?
Question by:Delphiwizard
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
  • 2
  • 2
  • 2
LVL 34

Expert Comment

ID: 40504561
CAVEAT: Make a backup first!

EXECUTE sp_msforeachtable 'DELETE FROM ? WHERE IsVerwijderd = 1;';

Open in new window

LVL 50

Accepted Solution

Vitor Montalvão earned 500 total points
ID: 40504629
Try this script:

SELECT @CommDEL = @CommDEL + N'DELETE FROM ' + SCHEMA_NAME(o.schema_id) + '.' + + ' WHERE IsVerwijderd=1;' + CHAR(13) + CHAR(10) 
FROM sys.objects o
	INNER JOIN sys.columns c ON c.object_id=o.object_id
WHERE o.object_id > 255 AND o.type='U' AND'IsVerwijderd'

EXEC sp_executesql @CommDEL 

Open in new window


Author Comment

ID: 40504645
@ste5an: Your solution does do the job, but it's not very clean.
As there are tables that do not contain field IsVerwijderd, executing this code will generate numerous errormessages.
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40504651
As there are tables that do not contain field IsVerwijderd, executing this code will generate numerous errormessages.
That's why I wrote the script I posted. Only runs the delete command on tables that has the field.
LVL 34

Expert Comment

ID: 40504684
You didn't ask for a clean version... There can be always errors caused by triggers or permissions or referential integrity.

The only clean solution is to script a DELETE per table without dynamic SQL. In the correct order regarding to FK relationships and containing the necessary conditions to avoid triggers or permission errors.

Or in simple  words: There is no general clean solution.

Author Closing Comment

ID: 40512788
This works fine for me. Thank you.

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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