Folder related queries

Unlink folders:
update dm_folder object unlink ‘/Cabinet/Folder1/Folder2/…./Parent Folder’ where r_object_id = ‘0bxxxxxxxxxxxxxx’

List folder path and all documents in given path

select distinct dm.r_object_id, dm.object_name, dm.a_category, dm.r_creation_date, dm.r_modify_date, dm.r_object_type, f.r_folder_path from dm_document dm, dm_folder f where FOLDER(ID(‘0b01bd6f805353a4’), DESCEND) AND dm.r_object_type in(‘w_doc’,’w_media’) AND f.r_folder_path like ‘%/My Location%’ AND dm.i_folder_id = f.r_object_id ORDER by f.r_folder_path ENABLE (ROW_BASED)
 

Content Size Note:
r_content_size: Size, in bytes, of the first content file associated with the document. This property cannot record content sizes greater than 2GB. Examine r_full_content_size to obtain the size of content larger than 2GB.

Cabinet Content Size (Method 1)

Determine the amount of content in a particular cabinet.  Replace ‘/Temp’ with the cabinet of your choice.  You can also use the folder() DQL function instead of cabinet().

select sum(r_full_content_size)/1024 as cnt_size_kb from dm_sysobject (all) where cabinet(‘/Temp’,descend);

Cabinet Content Size (Method 2)

A second way to determine the amount of content in a particular cabinet.  This one summarizes and displays the size of the content in each individual sub-folder.  Replace ‘/Temp’ with the cabinet of your choice.  You can also use the folder() DQL function instead of cabinet().  Be aware, this query can take a long time to run.

select f.object_name as folder_name_name, sum(d.r_full_content_size)/1024 as cnt_size_kb from dm_folder f, dm_sysobject (all) d where any d.i_folder_id = f.r_object_id and cabinet(‘/Temp’, descend) group by f.object_name order by object_name;

This query would be much cooler if it could return the r_folder_path for the folder instead of the object_name.  As it is, with a lot of sub-folders, it is easy to lose context.  If you can figure out how to return r_folder_path I’ll post an update.

Another way
SELECT fr.r_folder_path, count(*) as cnt, sum(r_full_content_size)/1024 as sizeKB FROM dm_sysobject s, dm_sysobject_r sr, dm_folder_r fr, dm_document dm WHERE s.i_is_deleted = 0 AND sr.i_position = -1 AND fr.i_position = -1 AND sr.r_object_id = s.r_object_id AND fr.r_object_id = sr.i_folder_id AND dm.r_object_id = s.r_object_id AND FOLDER(‘/Temp’, descend) GROUP BY fr.r_folder_path

Get An Object’s Folder Path

Get an object’s folder path.  Replace ‘My Document’ with the object name you are looking for.

select r_folder_path from dm_folder where r_object_id in (select i_folder_id from dm_sysobject where object_name = ‘My Document’);