Truncate all DIXF staging tables in MsDyn365FO

I recently had the challenge to delete all data from data import export framework (DIXF) staging tables in order to reduce the size of a database backup. The problem was that we had too much data in the staging table, so delete from didn´t complete with an acceptable duration. To solve the problem I developed the following SQL script via SQL Server Management Studio (SSMS):

IF object_id(‘tempdb..#TMP’) IS NOT NULL

DROP TABLE #TMP;

CREATE TABLE #TMP (

TableName [nvarchar](250)

);

DECLARE cur CURSOR

FOR

SELECT A.SQLNAME

 FROM SQLDICTIONARY A

  WHERE A.FIELDID = 0

AND A.FLAGS = 0                

AND A.NAME like ‘%Staging’

 OPEN cur;

 DECLARE @TableName [nvarchar](250);

 FETCH NEXT

FROM cur

INTO @TableName;

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #TMP (TableName)

VALUES (@TableName);                

FETCH NEXT

FROM cur

INTO @TableName;

END;

CLOSE cur;

DEALLOCATE cur;

DECLARE cur CURSOR

FOR

SELECT TableName

FROM #TMP;

OPEN cur;

FETCH NEXT

FROM cur

INTO @TableName;

WHILE @@FETCH_STATUS = 0

BEGIN

 DECLARE @_SQL NVARCHAR(4000)

 SET @_SQL = N’TRUNCATE TABLE ‘ + QUOTENAME(@TableName)

 PRINT (CHAR(250) + ‘Removing from ‘ + @TableName + ‘…’)

 EXEC SP_EXECUTESQL @_SQL

FETCH NEXT

FROM cur

INTO @TableName;

END;

CLOSE cur;

DEALLOCATE cur;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s