I was recently asked to find all custom folders in a cabinet which may have other nested folders, but no documents. In other words, folders can have other folders, but not documents.
In order to find empty folders, with no documents or sub-folders, is straightforward:
select * from dm_folder where i_reference_cnt=0
But what we are looking for are folders which may contain other folders, but none of the sub-folders should have a document.
I came up with the following query. Keep in mind that this query can be improved upon, for example by using joins. I only need to run it once so I have no need for performance based improvements at this time.
select
r_object_id, object_name, r_folder_path, r_creation_date, r_modify_date
from dm_folder //or custom dm_folder subtype
where r_object_id not in //Returns all folders which are not in the list of ancestor folders (folder hierarchy) as returned below
(
select distinct (i_ancestor_id) from dm_folder where r_object_id in //Returns all ancestor folders in the hierarchy of the parent folders below
(
select distinct(i_folder_id) from dm_document WHERE <your custom where clause> //Returns all parent folders which contain documents
)
)
and any r_folder_path is not nullstring
and folder('/Background Investigation', descend)
enable (ROW_BASED) //Needed when querying repeating attributes such as r_folder_path above
Tech Kraft 
Leave a comment