Query: Show all Data Templates in active use

« Return to Notes Listing

Author:G. Eggold
Last Updated:August 25, 2015 11:25 AM
This should give you the distinct list of templates used in DataList (26) and DataDetail (27) blocks for active versions.
 
SELECT DISTINCT DataTemplate.*
  FROM DataTemplate
  JOIN (SELECT Blocks .DocID, Version, BlockData.value( '(BlockData/Elements/*/TemplateID/text())[1]' , 'INT' ) AS TemplateID
          FROM Blocks
          JOIN Document
            ON Document. DocID = Blocks .DocID
            AND Document. ActiveVersion = Blocks .Version
          WHERE BlockTypeID IN (26 ,27)) DataBlocksWithTemplates
    ON DataBlocksWithTemplates .TemplateID = DataTemplate .TemplateID
top