SQL Script to clear data from the Markit EDM Data Flow source monitor tables.

ClearDataFlowSourceMonitor.sql

-- will contain data about the data flow source monitor tables
DECLARE @tableInfo TABLE (
  table_id INT identity(1, 1), -- a unique identifier we will use to loop over the tables
  table_name NVARCHAR(max)     -- the name of the table in question, used to build the dynamic query while in the loop
  )

-- popuplate the table with the names of tables used to store source monitor info for the data flows
INSERT @tableInfo
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'cadis_proc'
  AND table_name LIKE 'DFP_%_SRCMON_%'

-- used to loop
DECLARE @tableIdCounter INT = 1 

-- used to store the dynamic query
DECLARE @query NVARCHAR(max) = ''

WHILE (
    @tableIdCounter <= (
      SELECT count(*)
      FROM @tableInfo
      )
    )
BEGIN
  SET @query = 'truncate table CADIS_PROC.' + (
      SELECT table_name
      FROM @tableInfo
      WHERE table_id = @tableIdCounter
      )

  PRINT @query

  EXEC (@query)

  SET @tableIdCounter = @tableIdCounter + 1
END