jhorsman
10/5/2017 - 2:53 PM

Query to check installed item types in SDL Web Content Manager database

Query to check installed item types in SDL Web Content Manager database. This information can be used to determine if there are legacy items used. Also see https://gist.github.com/ea710455b86df711b5aea971ccd54b83

queries.txt
content_copyfile_download
Installed item types
--------------------
Check with legacy item types are used. 

SELECT [ID]
      ,[SORT_ORDER]
      ,[TABLE_NAME]
      ,[NAME]
  FROM [Tridion_cm].[dbo].[ITEMTYPES]
  ORDER BY ID



Used Item types and sub types
-----------------------------
Check which item types are used and count them. This gives an general idea of technologies used, and specifically if legacy techologies like VBScript are used.

SELECT TOP 1000 [I].[ITEM_TYPE]
      ,[I].[SUB_TYPE]
      ,COUNT([I].[ID]) as COUNT
  FROM [Tridion_cm].[dbo].[ITEMS] I
  WHERE VERSION = (SELECT MAX(VERSION) FROM [Tridion_cm].[dbo].[ITEMS] I2 WHERE I2.PUBLICATION_ID = I.PUBLICATION_ID AND I2.ITEM_REFERENCE_ID = I.ITEM_REFERENCE_ID)
  GROUP BY [ITEM_TYPE], [SUB_TYPE]
  ORDER BY [I].[ITEM_TYPE], [I].[SUB_TYPE]