[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • Last Modified:

Delete all records from all tables - conditionally

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
Stef Merlijn
Asked:
Stef Merlijn
  • 2
  • 2
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
CAVEAT: Make a backup first!

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

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Stef MerlijnDeveloperAuthor Commented:
@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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
Stef MerlijnDeveloperAuthor Commented:
This works fine for me. Thank you.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now