OK, this SQL will list all Resources with the specified Class Guid(s) and the folder path to where they reside. By default, it will list all user-created Managed Delivery Policies. I also included the Guid for Filters if you want to change it to that (in the comments). Hopefully the comments make sense, if not, feel free to ask. :)
This might be a little overkill, using the nested SQL Cursors and all, but it works :)
DECLARE @ResourceName NVARCHAR(MAX)
DECLARE @BaseFolderGuid UNIQUEIDENTIFIER
DECLARE @FolderPath NVARCHAR (MAX)
DECLARE @ParentFolderGuid UNIQUEIDENTIFIER
DECLARE @Count INT
DECLARE @Folder NVARCHAR (MAX)
DECLARE @Results TABLE ([Resource] NVARCHAR(MAX), [Path] NVARCHAR (MAX))
SET @FolderPath = NULL
SET @ParentFolderGuid = NULL
SET @Count = 0
SET @BaseFolderGuid = NULL
SET @ResourceName = NULL
DECLARE resourceCursor CURSOR
FOR
SELECT it.[Name], fd.Guid
FROM vNonResourceItem it
JOIN ItemFolder ifd ON ifd.[ItemGuid] = it.[Guid]
JOIN vFolder fd ON fd.[Guid] = ifd.[ParentFolderGuid]
WHERE 1 = 1
/***********************************************************************************************
* This is the key filter that determines which resources are included in the search.
* By default, it is Managed Software Delivery policies (there are 2 Class Guids required to get ALL Managed Delivery policies)
* If you wish to find all Filters, then you would substitute the Guid 'AECA2D91-C615-4D99-BAD5-612CA159DA4F'
*************************************************************************************************/
AND it.[ClassGuid] IN ('2D3A170E-5028-4570-BA0C-3DB775CB8BDE','212E1D10-4905-4CDB-A90A-81AE77E9DE9A') --Change Resource Guids here!!
/***********************************************************************************************
* This filter only includes items which are User created. Comment out this line if you
* wish to include items created by the SMP as well.
*************************************************************************************************/
AND it.[Attributes] = 0
/*************************************************************************************************
* Nested SQL cursors to iterate through all resources returned by previous query. Second cursor
* iterates through the folder structure in which the resource resides and builds a single Path
*
* No user modification required if changing the ResourceType above
*************************************************************************************************/
OPEN resourceCursor
FETCH NEXT FROM resourceCursor
INTO @ResourceName, @BaseFolderGuid
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE myCursor CURSOR
FOR SELECT ParentFolderGuid FROM FolderBaseFolder WHERE FolderGuid = @BaseFolderGuid ORDER BY Depth DESC
SET @FolderPath = ''
SET @Folder = ''
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @ParentFolderGuid
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ParentFolderGuid <> 0x
BEGIN
SET @Folder = (SELECT Name
FROM vFolder
WHERE Guid = @ParentFolderGuid)
SET @FolderPath = @FolderPath + '/' + @Folder
END
FETCH NEXT FROM myCursor INTO @ParentFolderGuid
END
CLOSE myCursor
DEALLOCATE myCursor
INSERT INTO @Results
VALUES(@ResourceName, @FolderPath)
SET @Count = (@Count + 1)
FETCH NEXT FROM resourceCursor INTO @ResourceName, @BaseFolderGuid
END
CLOSE resourceCursor
DEALLOCATE resourceCursor
SELECT * FROM @Results