CTE query to print out the ImageRight file types and the folder/document heirarchy

ImageRight File Heirarchy.sql

USE [ImageRight]
GO

WITH Source as (
	SELECT FT.[filetypeid]
		,FT.[itemid]
		,FT.[parentid]
		,FT.[single]
		,FT.[objecttypeid]
		,CASE O.[classid] WHEN -3 THEN 'File' WHEN -2 THEN 'Folder' WHEN -1 THEN 'Document' END as [type]
		,O.[name] as ObjectName
		,O.[description] as ObjectDescription
	FROM [dbo].[FileTemplate] FT
	JOIN [dbo].[ObjectType] O ON FT.objecttypeid = O.typeid
), Tree AS (
	SELECT CAST(S.ObjectName + ' - ' + S.ObjectDescription as varchar(255)) as Name
	, S.*
	, 1 as [Level]
	, CAST(S.ObjectName + ' - ' + S.ObjectDescription as varchar(255)) as Sort
	FROM Source S where parentid is null
	UNION ALL
	SELECT CAST(REPLICATE('  ', T.Level) + S.ObjectName + ' - ' + S.ObjectDescription as varchar(255)) as Name
	, S.*
  , T.[Level] + 1
	, CAST(T.Sort + '\' + S.ObjectName + ' - ' + S.ObjectDescription as varchar(255)) as Sort
	FROM Source S 
	JOIN Tree T ON S.parentid = T.itemid and S.filetypeid = T.filetypeid
) select * from Tree order by Sort