The following query will list the contents of all item folders found within the console:
SELECT vi1.Name As Folder,vi2.Name AS Item FROM vItem vi1 JOIN vItemFolder vif ON vif.ParentFolderGuid = vi1.[Guid] JOIN vItem vi2 ON vi2.[Guid] = vif.ItemGuid ORDER BY Folder,Item ASC --WHERE vif.ParentFolderGuid = 'folderguid' --WHERE vi1.Name = 'foldername'
If you wish to use one of the WHERE clauses, then make sure you remark (--) the ORDER BY line.
Hi Nico,
great job, very useful sql query.
Hi,
I created a stored procedure to get all objets within a specified folder and all its sub-folders (to get tasks in my exemple), with the full hierarchical folder path :
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_DMI_fnGetTasksWithPathInFolder]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[_DMI_fnGetTasksWithPathInFolder] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE FUNCTION [dbo].[_DMI_fnGetTasksWithPathInFolder] ( @MainFolderGuid uniqueidentifier = '455ae0db-ec74-455b-b262-89421c96908d' -- By default the root folder "Jobs and Tasks" ,@ExcludeFolderGuid uniqueidentifier = '00000000-0000-0000-0000-000000000000' -- By default no exclusion ) RETURNS @Tasks table (FolderPath nvarchar(255), TaskName nvarchar(200), TaskGuid uniqueidentifier not null)
BEGIN
------------------------------------------------------------------- -- Creation of the path hierarchy of the tasks in the specified folder ------------------------------------------------------------------- declare @FolderGuid uniqueidentifier declare @FolderPath nvarchar(255) = '' declare @PFolderGuid uniqueidentifier declare @PFolderName nvarchar(200)
-- Browse all folders included in the specified folder (except exclusion) declare c_folders CURSOR FOR select distinct fol.ItemGuid as [FolderGuid] from ItemFolder fol inner join FolderBaseFolder froot on froot.FolderGuid=fol.ParentFolderGuid left join FolderBaseFolder fexcept on fexcept.FolderGuid=fol.ItemGuid and fexcept.ParentFolderGuid=@ExcludeFolderGuid where fol.IsFolder=1 and froot.ParentFolderGuid=@MainFolderGuid and (@ExcludeFolderGuid='00000000-0000-0000-0000-000000000000' or fexcept.Depth is null) open c_folders FETCH NEXT FROM c_folders into @FolderGuid WHILE @@FETCH_STATUS = 0 BEGIN
-- Browse parent folders of the selected folder, starting from root folder declare c_parents CURSOR FOR select f.Guid as [PFolderGuid], cast(f.Name as nvarchar(200)) as [PFolderName] from FolderBaseFolder parents inner join vFolder f on f.Guid=parents.ParentFolderGuid where parents.FolderGuid=@FolderGuid order by parents.Depth desc open c_parents FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName -- Move until the main specified folder WHILE @PFolderGuid<>@MainFolderGuid BEGIN FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName END -- Add name of the folder at the right side of its parents already written in the variable (main specified folder excluded) FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName WHILE @@FETCH_STATUS = 0 BEGIN SET @FolderPath = @FolderPath + @PFolderName + '\' FETCH NEXT FROM c_parents into @PFolderGuid, @PFolderName END close c_parents deallocate c_parents
-- Insert all tasks included directly in the selected folder, with their folder path (except specified exclusion) INSERT INTO @Tasks select @FolderPath as [FolderPath], i.Name as [TaskName], i.Guid as [TaskGuid] from ItemFolder tsk inner join Item i on i.Guid=tsk.ItemGuid -- Exclusion of the hidden tasks where i.Attributes=0 and tsk.IsFolder=0 and tsk.ParentFolderGuid=@FolderGuid SET @FolderPath='' FETCH NEXT FROM c_folders into @FolderGuid END close c_folders deallocate c_folders
RETURN
END
GO