;WITH folderHierarchy (CollectionID,[Name],ParentCollectionID,[Path]) AS ( SELECT CollectionID, [Name], ParentCollectionID, CAST('/'+[Name]+'/' AS VARCHAR(MAX)) AS [Path] FROM (SELECT CollectionID, [Name], ParentCollectionID FROM v_Collection INNER JOIN v_CollectToSubCollect ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1 WHERE ParentCollectionID = 'COLLROOT' UNION ALL SELECT child.CollectionID, child.Name, child.ParentCollectionID, parent.[Path]+child.[Name]+'/' AS [Path] FROM (SELECT CollectionID, [Name], ParentCollectionID FROM v_Collection INNER JOIN v_CollectToSubCollect ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS child INNER JOIN folderHierarchy AS parent ON parent.CollectionID = child.ParentCollectionID ) SELECT fldr.CollectionID, fldr.Name, fldr.Path, v_UserInstancePermissionInfo.UserName FROM folderHierarchy AS fldr INNER JOIN v_UserInstancePermissionInfo ON fldr.CollectionID = v_UserInstancePermissionInfo.InstanceKey ORDER BY fldr.Name
SCCM SQL Query : to find path of collection and creator
Advertisements