Solved

Delete all records from all tables - conditionally

Posted on 2014-12-17
6
46 Views
Last Modified: 2014-12-22
Hi,

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?
0
Comment
Question by:Delphiwizard
  • 2
  • 2
  • 2
6 Comments
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
CAVEAT: Make a backup first!

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

Open in new window

0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
Try this script:
DECLARE @CommDEL NVARCHAR(MAX)=N''

SELECT @CommDEL = @CommDEL + N'DELETE FROM ' + SCHEMA_NAME(o.schema_id) + '.' + o.name + ' 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 c.name='IsVerwijderd'

EXEC sp_executesql @CommDEL 

Open in new window

0
 

Author Comment

by:Delphiwizard
Comment Utility
@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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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.
0
 

Author Closing Comment

by:Delphiwizard
Comment Utility
This works fine for me. Thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

16 Experts available now in Live!

Get 1:1 Help Now