Handy MEDM Query: Get Data Inspector Tests

This Markit EDM query (valid as of v18.2.18) outputs the following columns for every rule on every non-deleted Data Inspector:

  • DI_ID – Data Inspector unique identifier.
  • DI_NAME – Data Inspector name.
  • DI_OUTPUT_SCHEMA – Data Inspector target table schema.
  • DI_OUTPUT_TABLE – Data Inspector target table name.
  • TEST_NAME – Guess.
  • TEST_DEFINITION – A short XML snippet expressing the test logic. Pretty human-readable, especially if you click to it from inside SSMS.
SELECT 
  DI.IDENTIFIER AS DI_ID,
  DI.NAME AS DI_NAME,
  DI.[DEFINITION].value('(CadisXml/Content/FinalOutput/Table/Owner)[1]', 'nvarchar(128)') AS DI_OUTPUT_SCHEMA,
  DI.[DEFINITION].value('(CadisXml/Content/FinalOutput/Table/Name)[1]', 'nvarchar(128)') AS DI_OUTPUT_TABLE,
  DIT.TEST_DEFINITION.value('(Field/Name)[1]', 'nvarchar(128)') AS TEST_NAME,
  DIT.TEST_DEFINITION.query('.') AS TEST_DEFINITION
FROM 
  CADIS_SYS.DI_INSPECTION DI
  CROSS APPLY DI.[DEFINITION].nodes('/CadisXml/Content/InspectionFields/Flds/Item') AS DIT(TEST_DEFINITION)
WHERE
  DI.OBSOLETE = 0
ORDER BY
  DI_NAME,
  TEST_NAME

Leave a Reply