Delete all data in tables except system versioned (history) tables-CodePudding

I am trying to delete all data in all tables except system versioned (because we can't). This is part of an integration test that clears data before running each test.

The following statement returns 1 for history tables (the ones that we cannot execute delete from on them.

SELECT OBJECTPROPERTY(OBJECT_ID('table_name'), 'TableTemporalType')

So my attempt was as follows:

-- Remove check constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
-- Delete data
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableTemporalType'') != 1 DELETE FROM ?'
-- Restore check constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

However, I am still getting the error:

Cannot delete rows from a temporal history table 'dbo.table_name'.

I am not sure what I am doing wrong!

Any hints are appreciated!

CodePudding user response：

I would do this by generating some dynamic sql using sys.tables. Something like this should be pretty close to what you are trying to do.

declare @sql nvarchar(max) = ''

select @sql = @sql   'delete '   name   ';'
from sys.tables
where temporal_type = 0

select @sql
--uncomment the line below when you are ready to blow away all your data
--exec sp_executesql @sql